Postgres 'after insert or update' trigger isn't firing

Question

I've migrated our database from Oracle to Postgres. One of the steps is to save a copy of the record's status history. I do this via an INSERT, UPDATE trigger on the main table. The trigger checks to see if the status has changed and, if so, adds a record to the status history table.

CREATE OR REPLACE FUNCTION SAMPLE_STATUS_HISTORY_Trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF( old.STATUS_CODE != new.STATUS_CODE ) THEN
    INSERT INTO SAMPLE_STATUS_HISTORY( analyte_status_history_id, sample_result_id, STATUS_CODE, status_date, status_user_id )
      VALUES( nextval('SAMPLE_ANALYTE_STATUS_HIST_SEQ'), new.sample_result_id, new.STATUS_CODE, new.status_date, new.status_user_id );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER TRG_SAMPLE_ANALYTE_STATUS_HIST
    AFTER INSERT OR UPDATE ON SAMPLE_RESULTS
    FOR EACH ROW
    EXECUTE PROCEDURE SAMPLE_STATUS_HISTORY_Trigger()
;

I've ensured the trigger is bound to the table.

The main application is a .NET MVC app using NHibernate. Things work within a transaction, but the transactions are working for the main table's insert/updates.

My only guess is that I have a syntax error in the trigger logic but I'm not seeing the error if I manually run an insert or update statement.

JGH JGH · Accepted Answer · 2020-06-09T15:38:40

For insert, OLD.? is null so the inequality check will also return null.

Instead, use

IF( old.STATUS_CODE IS DISTINCT FROM new.STATUS_CODE ) THEN

Postgres 'after insert or update' trigger isn't firing

2 Answers