Hi,

I've created a trigger to capture 2 fields (f1 and f2).
I did and check by using If update(f1) or update(f2) then
only firing the trigger to create old and new image.
But, this trigger will be fired eventhounght the f1 and f2 value did not
change.
How do I check the changes before fire the update trigger.

Please advise.

Ben

Join the INSERTED and DELETED virtual tables in your trigger code and
compare the column values. Remember that a trigger fires once per update not
once per row - there may be more than one row in those tables.

Hi David,

Where can I find a documentation or sample code on this ?

Thank you in advance.

Benny

Show quoteHide quote

Read introduction here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_0lo3.asp

A few samples are available under "CREATE TRIGGER".


ML

Unfortunately the example code in the CREATE TRIGGER topic isn't very good.
The join will look something like this

....
FROM Inserted AS I
JOIN Deleted AS D
  ON I.key_col  = D.key_col
WHERE I.col1 <> D.col1
     OR I.col2 <> D.col2
   ...

assuming your columns are not nullable.

Dangers of update statement
Automating database changes
[OT] SQL Humor
composite key structure
Need Query Help