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

Re: How to notice column changes in trigger

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to notice column changes in trigger
Date: 2003-03-06 16:01:24
Message-ID: 1046966484.20270.94.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 2003-03-06 at 15:00, Andreas Pflug wrote:
> How can I detect whether a column was changed by an update command 
> inside a trigger?
> 
> create table test(a int, b int, c int, primary key(a))
> 
> b and c should be updated inside an update trigger if not modified by 
> the statement itself
> 
> 1) update test set a=0 -> trigger does its work
> 2) update test set a=0, b=1, c=2 -> trigger does nothing
> 3) update test set a=0, b=b, c=c -> trigger does nothing, but content of 
> a and b dont change either although touched
> 
> What I'm looking for is something like
> IF NOT COLUMN_TOUCHED(b) THEN ...
> For MSSQL, this would be coded as  IF NOT UPDATE(b) ..

  IF NEW.b = OLD.b OR (NEW.b IS NULL AND OLD.b IS NULL) THEN
    -- b has not changed
    ...
  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
                 ========================================
     "The LORD is my light and my salvation; whom shall I 
      fear? the LORD is the strength of my life; of whom 
      shall I be afraid?"           Psalms 27:1 


In response to

Responses

pgsql-performance by date

Next:From: Andreas PflugDate: 2003-03-06 16:09:40
Subject: Re: How to notice column changes in trigger
Previous:From: Tom LaneDate: 2003-03-06 15:25:36
Subject: Re: pgsql-server/ /configure /configure.in rc/incl ...

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