CODEWITHSUNDEEP
postgresql
Eugen Konkov
Eugen Konkov

Reputation: 25143

`instead of update` trigger: How to distinguish when user provides new value for the column and when does not?

Inside the trigger NEW.field value is always has <some value> despite on that column appears on SET list of UPDATE statement or does not:

UPDATE test SET name = 'abc', field = <some value>;
UPDATE test SET name = 'abc';

For both queries in trigger I will get field with <some value>.

Is there a way to distinguish these two queries from inside trigger?

Upvotes: 3

Views: 85

Answers (1)

Hambone
Hambone

Reputation: 16377

I would think you could just compare the NEW value to the OLD. If it's different, you know the field was set.

Unless you wanted to capture when there was an update to the field, even if the field value did not change, I would think this captures it.

CREATE OR REPLACE FUNCTION test_update_trigger()
  RETURNS trigger AS
$BODY$
BEGIN

  if (OLD.field is null and NEW.field is not null or
      OLD.field is not null and NEW.field is null or
      OLD.field != NEW.field) then
    -- do something
  end if;

  return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Upvotes: 1

Related Questions