Thread: 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)