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

sql-server, t-sql, triggers

Solution

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.

Problem

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?

Original source