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