1

I am trying to create a trigger (PostgreSQL 9.4) 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?

  • Since there is no GIS-centric component to this task, Database Administrators might be a better place to ask this. Either way, you should certainly Edit the question to specify the exact release of PostgreSQL in use. – Vince Jun 21 '19 at 22:15
  • Thanks Vince, will do that. – Lisdengard Jun 21 '19 at 22:16