How to determine if anything changed in update tri

问题:

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?

回答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.

回答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