How to determine if anything changed in update trigger in t-sql

Question

How can I determine if something has changed in UPDATE trigger? For example I have table named person with only one column NAME which contains value 'Mike'. If I run

UPDATE person SET NAME = 'Mike' 

how can I determine in the update trigger that nothing has changed? I know about UPDATE(col) statement, but I don't want to iterate over columns. Is there any other way to accomplish this?

Solution 1

Update(column) merely states that column participated in update, but not that its value has changed. For instance,

update Person SET Name = Name

yields true in update(name) even though Name has not been changed in any row.

To check if new values differ from old ones, you would employ except because except will remove rows from top set that exist in bottom set. As person table probably has primary key there is not a danger of removing changed item that has a counterpart in deleted. However if you decide to change * to a list of interesting columns be sure to include primary key.

insert into logTable (ID)
select a.ID
from
(
   select * from Inserted
   except
   select * from Deleted
) a

Added benefit is that this works for inserts too because Deleted will be empty and all rows from inserted will be returned.

Solution 2

Referring to Arion's answer above:

Be sure to compare records by their primary key when SELECTing from a JOIN since INSERTED and DELETED tables may contain more than one record, which - if ignored - may result in both wrong query results and negative impact on DB performance.

-- Anrion's answer - slightly modified
CREATE TRIGGER UpdatedTriggerName
ON person -- table name
AFTER UPDATE
AS 
IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D
            -- make sure to compare inserted with (same) deleted person
            ON D.ID = I.ID 
            AND D.NAME <> I.NAME -- only persons with changed name
    )
print 'something'
GO

Comments

[Stefan Steiger]

And there, I finally understand what EXCEPT and INTERSECT are thought to be used for. Note: This only works correctly, if the table has a primary key set. if there is no primary key, and 20 equal records get updated, this will write 1 record into the log table, not 20. Also, this fails width non-nullable computed column, if that column gets written into the log table (computed column has value NULL in insert/update trigger). Also, EXCEPT doesn't work with text,ntext,image,xml, geography & geometry. Strangely, it works with hierarchyid, even though the docs say it doesn't work with CLR-types.

[drizin]

If name was null and changed to a new value (or vice versa) this won't work.