TSql Trigger needs to fire only on columns whose values have changed

Question

I wrote a trigger that needs to do some different work on a table based on which columns in a row actually updated. I accomplished this using

IF UPDATE(column-name)

That part works fine. It turns out, however, that there are other parts of the code that update rows by setting every single value whether the value actually changed or not and this causes the trigger to fire for parts that were "updated" but whose values did not actually change at all.

As changing the code that's causing this is probably not an option, is there an easier way to prevent this other than having to compare between the INSERTED and DELETED tables (in which case the IF UPDATEs are meaningless)?

Answer 1

Not that I know of. INSERTED and DELETED are there for a reason, and comparing between them seems the best option to me.

IF UPDATE() just tells you if an insert or update attempt was made, and doesn't even reflect if that attempt was successful or not.

Answer 2

The column was updated. That the old value and the new value were the same is just a detail. I think your only solution is to compare the values between inserted and deleted pseudo-tables.

Answer 3

As others have said, you have to use the INSERTED and DELETED tables.

Here's what I've done, instead of :

IF UPDATE(col1) OR UPDATE(col2))
UPDATE table1
SET col3 = 1
FROM INSERTED
WHERE table1.Id = INSERTED.Id

I have done:

IF UPDATE(col1) OR UPDATE(col2))
 UPDATE table1
 SET col3 = 1
 FROM 
  (SELECT Id, col1, col2 FROM INSERTED 
   EXCEPT 
  SELECT Id, col1, col2 FROM DELETED) AS ALTERED
 WHERE table1.Id = ALTERED.Id

Using EXCEPT selects the distinct rows where the columns I am interested do not exist in the deleted (or original data) table (meaning the value of those columns has changed). I don't know if this is the best answer but it does seem to work.

Answer 4

IF EXISTS (SELECT *
          FROM
             INSERTED I JOIN DELETED D ON I.Key = D.Key
          WHERE
             I.Col <> D.Col)
...

or use a table variable to cache thus to avoid repeated use of I and D.

SELECT
    CASE WHEN I.Col1 <> D.Col1 THEN 1 ELSE 0 END AS Col1Diff,
    CASE WHEN I.Col2 <> D.Col2 THEN 1 ELSE 0 END AS Col2Diff,
    ...
FROM
    INSERTED I JOIN DELETED D ON I.Key = D.Key

or combine ideas to test all changes up front and exit the trigger