ON UPDATE trigger question

From: Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: ON UPDATE trigger question
Date: 2007-09-12 18:56:13
Message-ID: 20070912135613.167c55a4@sinkhole.intrcomm.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

If I create an ON UPDATE trigger run on each row after update, does
the trigger fire only on rows affected by the update or for all rows?

For example:

CREATE TRIGGER my_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE my_update_proc;

UPDATE my_table SET my_val = my_val * 2;

Will the trigger fire on rows that have NULL for my_val?

If so, would this be ok in the trigger proc to generically tell if
the row actually changed:

-- check if update affected the row
IF TG_OP = 'UPDATE' THEN
IF OLD = NEW THEN
RETURN NULL;
END IF;
END IF;

-- further processing here

Or would you have to compare each field in OLD, NEW to see if
anything actually changed?

Josh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message D. Dante Lorenso 2007-09-12 19:05:27 Re: Event-driven programming?
Previous Message Richard Huxton 2007-09-12 18:19:17 Re: Event-driven programming?