Message-ID: <4A67BFEC.7080906@free.fr>
Date: Thu, 23 Jul 2009 03:42:04 +0200
From: nha <lyondif02@free.fr>
Organization: Lyon, Rhone, France
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Chester C Young <chestercyoung@yahoo.com>
CC: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: how to tell if column set on update
References: <137458.25689.qm@web54304.mail.re2.yahoo.com>
	<4A66FB11.1000302@sympatico.ca>
In-Reply-To: <4A66FB11.1000302@sympatico.ca>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hello,

Le 22/07/09 13:42, Frank Bax a écrit :
> chester c young wrote:
>>> Le 20/07/09 15:19, chester c young a écrit :
>>>> within a trigger need to know if the UPDATE statement
>>> set a column.  the column might be set to the old value
>>> or a different value. [...]
>>>>
>>> If the column to test is known -- e.g. column MyCol --,
>>> NEW.MyCol and OLD.MyCol [...] can be compared.
>>
>> for example,
>> create table t1( c1 int, c2 int );
>> insert into t1 values( 1, 2 );
>>
>> 1) update t1 set c1=4 where c1=1;
>> 2) update t1 set c1=4, c2=2 where c1=1;
>>
>> each update results in the same row, but in the second update c2 was
>> actually set.
>> a trigger on the update - how do we know if c2 has been actually set
>> or not?
> 
> You cannot do that.  You can only detect that a value has changed.
> 

There would be one way to detect a value change but it is more related
to application management than to data manipulation. For the column c2,
it could be assumed that a flag is set when (a row of) c2 is updated.
Such a flag may be stored in a separate table (while it could also be
added to table t1) that is updated with a trigger based on update on t1.

Regards.
--
nha / Lyon / France.