Re: how to tell if column set on update

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.