Re: [ADMIN] How to execute my trigger when update certain columns

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Raymond Chui <Raymond(dot)Chui(at)noaa(dot)gov>
Cc: pgsql-sql(at)postgresql(dot)org, pgadmin-support(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] How to execute my trigger when update certain columns
Date: 2002-08-22 06:10:27
Message-ID: 1029996627.31594.1742.camel@linda
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-admin pgsql-general pgsql-sql

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(dot)Elphick(at)lfix(dot)co(dot)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

In response to

Browse pgadmin-support by date

  From Date Subject
Next Message 홍종근 2002-08-22 07:12:01 I hava a question about case-sensitive
Previous Message Tyler Mitchell 2002-08-21 19:02:04 Migration plugin not appearing

Browse pgsql-admin by date

  From Date Subject
Next Message Philip Rudling 2002-08-22 09:00:46 programatically obtaining table layout
Previous Message Tom Lane 2002-08-22 04:47:38 Re: OT: password encryption (salt theory)

Browse pgsql-general by date

  From Date Subject
Next Message Tommi Maekitalo 2002-08-22 06:13:08 Re: removing duplicates - sql
Previous Message Tom Lane 2002-08-22 05:28:57 Re: Default values, inserts, and rules...

Browse pgsql-sql by date

  From Date Subject
Next Message Rajesh Kumar Mallah. 2002-08-22 08:22:24 BITMAP INDEXES...
Previous Message Mark Stosberg 2002-08-22 02:20:54 Re: Event recurrence - in database or in application code ????