如何确定T-SQL中的更新触发器中是否有任何更改 [英] How to determine if anything changed in update trigger in t-sql

问题描述

如何确定UPDATE触发器中是否有更改？例如，我有一个名为person的表，其中只有一列NAME，其中包含值 Mike。如果我运行

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' 

如何确定更新触发器中没有任何变化？我知道
UPDATE（col）
语句，但是我不想遍历列。还有其他方法可以做到这一点吗？

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（column）仅声明该列参与了update，但没有声明其参与了更新价值已经改变。例如，

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

update Person SET Name = Name

即使在任何行中名称都没有更改的情况下，在update（name）中也会产生true。

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

附加的好处是，它也适用于插入，因为Deleted为空，将返回插入的所有行。

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

这篇关于如何确定T-SQL中的更新触发器中是否有任何更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！