ON UPDATE trigger question
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
On Wednesday 12 September 2007 15:56:13 Josh Trutwin wrote:
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?
I haven't tested what you asked (you can do that easily), but if this is your
concern and if you have a huge table you might want to add a WHERE
clause: "WHERE my_val IS NOT NULL".
--
Jorge Godoy <jgodoy@gmail.com>
On Wed, Sep 12, 2007 at 01:56:13PM -0500, Josh Trutwin wrote:
Or would you have to compare each field in OLD, NEW to see if
anything actually changed?
you dont have to compare all columns (at least not in 8.2 and newer).
please take a look at
http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/
make sure you'll also read comment from david fetter.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)