Sample Header Ad - 728x90

PostgreSQL Trigger to keep track of table changes

1 vote
2 answers
6461 views
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 (public).taxon table, no record is added to the history.taxon 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?
Asked by Lisdengard (113 rep)
Jun 22, 2019, 05:09 AM
Last activity: Nov 23, 2023, 08:36 AM