From: | ALT SHN <i(dot)geografica(at)alt-shn(dot)org> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Trigger to keep track of table changes |
Date: | 2019-06-22 15:25:18 |
Message-ID: | CAGFOAzw0sL93txQp=FWErqxgzj+i1ox-q3z1fgnsPcYgQiHd5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
n the end I solved this through 'Divide and conquer' - I split the trigger
into three parts and now it works (though I am not sure yet why the
original trigger does not work):
CREATE OR REPLACE FUNCTION taxon_history_delete() RETURNS trigger
AS $BODY$BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
RETURN old;
END IF;END;$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS $BODY$BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
VALUES ('UPDATE', current_timestamp, current_user, old.oid, old.taxon);
RETURN old;
END IF;END;$BODY$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION taxon_history_insert() RETURNS trigger AS $BODY$BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
RETURN new;
END IF;END;$BODY$
LANGUAGE plpgsql;
create TRIGGER taxon_history_delete
Before DELETE ON taxonFOR EACH ROW EXECUTE PROCEDURE taxon_history_delete();
create TRIGGER taxon_history_update
Before UPDATE ON taxonFOR EACH ROW EXECUTE PROCEDURE taxon_history_update();
create TRIGGER taxon_history_insert
AFTER INSERT ON taxonFOR EACH ROW EXECUTE PROCEDURE taxon_history_insert();
ALT SHN <i(dot)geografica(at)alt-shn(dot)org> escreveu no dia sábado, 22/06/2019 à(s)
11:33:
> I am trying to create a trigger (Postgres 9.6) to track changes made to a
> table. This is my approach:
>
> CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS
> $BODY$
> BEGIN
> IF TG_OP = 'DELETE' THEN
> INSERT INTO history.taxon(operacao, "data", tecnico, original_oid,
> taxon)
> VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
> RETURN old;
>
> ELSIF TG_OP = 'UPDATE' THEN
> INSERT INTO history.taxon(operacao, "data", tecnico, original_oid,
> taxon)
> VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
> RETURN old;
>
> ELSIF TG_OP = 'INSERT' THEN
> INSERT INTO history.taxon(operacao, "data", tecnico, original_oid,
> taxon)
> VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
> RETURN old;
> END IF;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER history_taxon
> AFTER INSERT OR UPDATE OR DELETE ON taxon
> FOR EACH ROW EXECUTE PROCEDURE taxon_history();
>
> However when something changes in the `taxon` table, no record is added to
> the `taxon_history` table. I also don´t get any error message so I am in
> the dark on why nothing is happening. What am I doing wrong?
>
> --
> ---------------------------------------------------------------
>
>
> *Sociedade de História Natural*
> Departamento de Informação Geográfica
> Polígono Industrial do Alto do Amial
> Pav.H02 e H06
> PORTUGAL
>
> i(dot)geografica(at)alt-shn(dot)org <laboratorio(at)alt-shn(dot)org>
> www.shn.pt
> www.alt-shn.blogspot.com
> Facebook <https://www.facebook.com/SociedadeDeHistoriaNatural?ref=hl>
>
>
--
---------------------------------------------------------------
*Sociedade de História Natural*
Departamento de Informação Geográfica
Polígono Industrial do Alto do Amial
Pav.H02 e H06
i(dot)geografica(at)alt-shn(dot)org <laboratorio(at)alt-shn(dot)org>
www.shn.pt
www.alt-shn.blogspot.com
Facebook <https://www.facebook.com/SociedadeDeHistoriaNatural?ref=hl>
TLM: 964138188
From | Date | Subject | |
---|---|---|---|
Next Message | Pól Ua Laoínecháin | 2019-06-29 16:46:08 | How to get non-existant values as NULL or 0? |
Previous Message | ALT SHN | 2019-06-22 09:33:55 | Trigger to keep track of table changes |