Fire trigger on update of columnA or ColumnB or ColumnC

Question

I have the code to fire a trigger only on an update of a single specific column. The trigger is used to fire a function that will raise a postgres "notify" event, which I am listening for and will need to test and validate the newly input details. There are many values on the account_details table which could be change which do not require an account validate, so a trigger on AFTER UPDATE only (without a when) is no good.

    CREATE TRIGGER trigger_update_account_details
    AFTER UPDATE ON account_details
    FOR EACH ROW
    WHEN (OLD.email IS DISTINCT FROM NEW.email) 
    EXECUTE PROCEDURE notify_insert_account_details();

But I want to fire the trigger if one of many columns change, something like

WHEN (OLD.email IS DISTINCT FROM NEW.email OR 
OLD.username IS DISTINCT FROM NEW.username OR 
OLD.password IS DISTINCT FROM NEW.password) 

But OR is not a valid keyword for a trigger. Trying to search for the keyword to use instead of OR doesn't seem to bring up anything due the nature of the word OR :-(

Answer 1

The WHEN clause of the trigger definition expects a boolean expression and you can use OR operators in it. This just works (as long as all columns exist in the table account_details). I am using similar triggers myself:

CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email    IS DISTINCT FROM NEW.email
   OR OLD.username IS DISTINCT FROM NEW.username
   OR OLD.password IS DISTINCT FROM NEW.password) 
EXECUTE FUNCTION notify_insert_account_details();

In Postgres 10 or older use the (misleading) key word PROCEDURE instead of FUNCTION. See:

• Trigger uses a procedure or a function?

Evaluating the expression has a tiny cost, but this is probably more reliable than the alternative:

CREATE TRIGGER ... AFTER UPDATE OF email, username, password ...

Because, quoting the manual:

A column-specific trigger (one defined using the UPDATE OFcolumn_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered. Conversely, a command such as UPDATE ... SET x = x ... will fire a trigger on column x, even though the column's value did not change.

ROW type syntax is shorter to check on many columns (doing the same):

...
WHEN ((OLD.email, OLD.username, OLD.password, ...)
       IS DISTINCT FROM
      (NEW.email, NEW.username, NEW.password, ...))
...

Or, to check for every visible user column in the row:

...
WHEN (OLD IS DISTINCT FROM NEW)
...

Answer 2

I don't think you need the WHEN clause. You can specify the columns in question in the UPDATE clause:

CREATE TRIGGER trigger_update_account_details
    AFTER UPDATE OF email, username, password ON account_details
    FOR EACH ROW
    EXECUTE PROCEDURE notify_insert_account_details();

Answer 3

The above solutions were not working for me properly. So after reading through documentation again. I found few things to take note of. BEFORE UPDATE ON - AFTER UPDATE ON triggers are executed differently. Since my procedure was returning the NEW record with updated value. It was not working in AFTER trigger and in BEFORE trigger, the OR statements inside WHEN clause needed to be enclosed by braces.

CREATE TRIGGER check_update
BEFORE UPDATE ON some_table
FOR EACH ROW
WHEN ((OLD.colum_name_1 IS DISTINCT FROM NEW.colum_name_1) OR (OLD.colum_name_2 IS DISTINCT FROM NEW.colum_name_2))
EXECUTE PROCEDURE update_updated_at_column();

And the procedure

CREATE OR REPLACE FUNCTION update_updated_at_column()
  RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = now();
      RETURN NEW;
  END;
  $$ language 'plpgsql';

Answer 4

I'm in a bit of a rush but here's the solution I went with. I wanted to updated a column named "receivedAt" anytime the "answer" column changes (yes, my columns are camel case and my tables are capitalized... don't ask...). I also wanted it to null out if the answer was nulled (edge case that shouldn't ever really happen). But I didn't want this trigger to fire every time any row updates at all, as that could be costly.

I decided to combine the strategies used above, trusting in Postgres to do what it's supposed to in a performant way. I think some of them kind of reinvent the wheel and are inefficient in that they will fire any time any update is made.

I use knex migrations to manage my database, so I'll just go ahead and paste the whole thing in here.

import { Knex } from 'knex';


export async function up(knex: Knex): Promise<void> {
  await knex.raw(`
  CREATE OR REPLACE FUNCTION question_update_received_at_when_answer_changes()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
  $$
  BEGIN
    NEW."receivedAt" = NOW();
    IF NEW."answer" IS NULL THEN
      NEW."receivedAt" = NULL;
    END IF;
    RETURN NEW;
  END;
  $$;

  DROP TRIGGER IF EXISTS trigger_question_answer_received_at ON "Question";
  CREATE TRIGGER trigger_question_answer_received_at
    BEFORE UPDATE OF "answer" ON "Question"
    FOR EACH ROW
    WHEN (OLD."answer" IS DISTINCT FROM NEW."answer")
    EXECUTE PROCEDURE question_update_received_at_when_answer_changes();
  `)
}


export async function down(knex: Knex): Promise<void> {
  await knex.raw(`
  DROP TRIGGER trigger_question_answer_received_at on "Question";
  DROP FUNCTION question_update_received_at_when_answer_changes;
  `)
}