Thread: How to execute my trigger when update certain columns but not all?

Let say I have a table has column1 and column2 and I made a trigger for
after INSERT OR UPDATE for each row to execute procedure my_function.

What I want is the trigger execute my_function only when column1 is
insert or
update, but not going to execute my_function when column2 is insert or
update.
How do I do that?

Thank Q very much

On Wed, 2002-08-14 at 13:09, Raymond Chui wrote:
> Let say I have a table has column1 and column2 and I made a trigger for
> after INSERT OR UPDATE for each row to execute procedure my_function.
>
> What I want is the trigger execute my_function only when column1 is
> insert or
> update, but not going to execute my_function when column2 is insert or
> update.

The trigger is executed unconditionally, so put the condition inside
my_function.

If it's an INSERT, column1 must be new, so:

IF TG_OP = ''INSERT'' OR
  (TG_OP = ''UPDATE'' AND
     (NEW.column1 != OLD.column1 OR
       (NEW.column1 IS NULL AND OLD.column1 IS NOT NULL) OR
       (NEW.column1 IS NOT NULL AND OLD.column1 IS NULL)
     )
  ) THEN
...
END IF;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For yourselves know perfectly that the day of the Lord
      so cometh as a thief in the night. For when they shall
      say, Peace and safety; then sudden destruction cometh
      upon them, as travail upon a woman with child; and
      they shall not escape."      I Thessalonians 5:2,3