Re: Trigger to keep track of table changes - Mailing list 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 taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_delete();

create TRIGGER taxon_history_update
Before UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();

create TRIGGER taxon_history_insert
AFTER INSERT ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_insert();