Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-adminpgsql-generalpgsql-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

pgsql-sql by date

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

pgsql-admin by date

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

pgadmin-support by date

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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group