Thread: how to tell if column set on update
within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a
differentvalue.
(want to make sure the app is sending all necessary values)
thanks
Hello, 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 differentvalue. > > (want to make sure the app is sending all necessary values) > > thanks > If the column to test is known -- e.g. column MyCol --, NEW.MyCol and OLD.MyCol -- respectively value of MyCol after UPDATE and value of MyCol before UPDATE -- can be compared. I may be wrong if you are looking for the column(s) that are really updated, not if such a column is updated or not. In this case, each column of the target table may be tested with their respective new and old values as well. Regards. -- nha / Lyon / France.
> 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. > > > > (want to make sure the app is sending all necessary > values) > > > > thanks > > > > If the column to test is known -- e.g. column MyCol --, > NEW.MyCol and > OLD.MyCol -- respectively value of MyCol after UPDATE and > value of MyCol > before UPDATE -- 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?
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. >>> (want to make sure the app is sending all necessary >> values) >>> thanks >>> >> If the column to test is known -- e.g. column MyCol --, >> NEW.MyCol and >> OLD.MyCol -- respectively value of MyCol after UPDATE and >> value of MyCol >> before UPDATE -- 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.
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.