Обсуждение: 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)