Trigger to keep track of table changes

From: ALT SHN <i(dot)geografica(at)alt-shn(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Trigger to keep track of table changes
Date: 2019-06-22 09:33:55
Message-ID: CAGFOAzx1drSmQ=+KQGPZqdO7aOPXzEePcxgyHxqTmrMn=Z2x4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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>

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message ALT SHN 2019-06-22 15:25:18 Re: Trigger to keep track of table changes
Previous Message Anthony E. Greene 2019-06-14 20:20:26 Re: ERROR when inserting csv values into sql table