如何确定t-sql中更新触发器中是否有任何更改

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的表，只有一个列NAME包含值'Mike'。如果我跑

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?

如何在更新触发器中确定没有任何更改？我知道UPDATE（col）语句，但我不想迭代列。有没有其他方法可以实现这一目标？

2 个解决方案

#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.

即使名称未在任何行中更改，更新（名称）中的结果为true。

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.

额外的好处是，这也适用于插入，因为Deleted将为空，并且将返回插入的所有行。

#2

Referring to Arion's answer above:

参考Arion上面的回答：

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.

由于INSERTED和DELETED表可能包含多条记录，因此在从JOIN中选择时，请确保按主键比较记录，如果忽略这些记录，可能会导致错误的查询结果和对DB性能的负面影响。

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

#1