SQL 服务器触发器中的 IF UPDATE()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2164282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IF UPDATE() in SQL server trigger
提问by Jason M
If there's:
如果有：
IF UPDATE (col1)
...in the SQL server trigger on a table, does it return true only if col1 has been changed or been updated?
...在表上的 SQL 服务器触发器中，仅当 col1 已更改或更新时才返回 true 吗？
I have a regular update query like
我有一个定期的更新查询，比如
UPDATE table-name
SET col1 = 'x',
col2 = 'y'
WHERE id = 999
Now what my concern is if the "col1" was 'x' previously then again we updated it to 'x'
would IF UPDATE ("col1")trigger return True or not?
现在我担心的是，如果“col1”之前是“x”，那么我们再次将其更新为“x”是否会IF UPDATE ("col1")触发返回 True？
I am facing this problem as my save query is generic for all columns, but when I add this condition it returns True even if it's not changed...So I am concerned what to do in this case if I want to add condition like that?
我面临这个问题，因为我的保存查询对所有列都是通用的，但是当我添加这个条件时，即使它没有改变，它也会返回 True ......所以我担心如果我想添加这样的条件，在这种情况下该怎么做?
回答by Remus Rusanu
It returns true if a column was updated. An update means that the query has SET the value of the column. Whether the previous value was the same as the new value is largely irelevant.
如果列已更新，则返回 true。更新意味着查询已经设置了列的值。以前的值是否与新值相同在很大程度上无关紧要。
UPDATE table SET col = col
it's an update.
这是一个更新。
UPDATE table SET col = 99
when the col already had value 99 also it's an update.
当 col 已经具有值 99 时，它也是一个更新。
回答by Ray
Within the trigger, you have access to two internal tables that may help. The 'inserted' table includes the new version of each affected row, The 'deleted' table includes the original version of each row. You can compare the values in these tables to see if your field value was actually changed.
在触发器中，您可以访问两个可能有帮助的内部表。“插入”表包括每个受影响行的新版本，“删除”表包括每一行的原始版本。您可以比较这些表中的值，以查看您的字段值是否已实际更改。
回答by HLGEM
What you do is check for different values in the inserted and deleted tables rather than use updated() (Don't forget to account for nulls). Or you could stop doing unneeded updates.
您所做的是检查插入和删除表中的不同值，而不是使用 updated() （不要忘记考虑空值）。或者你可以停止做不需要的更新。
回答by Bruce Pierson
Here's a quick way to scan the rows to see if ANY column changed before deciding to run the contents of a trigger. This can be useful for example when you want to write a history record, but you don't want to do it if nothing really changed.
这是在决定运行触发器的内容之前扫描行以查看是否有任何列更改的快速方法。例如，当您想写入历史记录时，这可能很有用，但如果没有真正改变，您又不想这样做。
We use this all the time in ETL importing processes where we may re-import data but if nothing really changed in the source file we don't want to create a new history record.
我们一直在 ETL 导入过程中使用它，我们可能会重新导入数据，但如果源文件中没有真正改变，我们不想创建新的历史记录。
CREATE TRIGGER [dbo].[TR_my_table_create_history]
ON [dbo].[my_table] FOR UPDATE AS
BEGIN
--
-- Insert the old data row if any column data changed
--
INSERT INTO [my_table_history]
SELECT d.*
FROM deleted d
INNER JOIN inserted i ON i.[id] = d.[id]
--
-- Use INTERSECT to see if anything REALLY changed
--
WHERE NOT EXISTS( SELECT i.* INTERSECT SELECT d.* )
END
Note that this particular trigger assumes that your source table (the one triggering the trigger) and the history table have identical column layouts.
请注意，此特定触发器假定您的源表（触发触发器的表）和历史记录表具有相同的列布局。
回答by zloctb
Trigger:
触发：
CREATE TRIGGER boo ON status2 FOR UPDATE AS
IF UPDATE (id)
BEGIN
SELECT 'DETECT';
END;
Usage:
用法：
UPDATE status2 SET name = 'K' WHERE name= 'T' --no action
UPDATE status2 SET name = 'T' ,id= 8 WHERE name= 'K' --detect
回答by Oliver
To shortcut the "No actual update" case, you need also check at the beginning whether your query affected any rows at all:
要简化“无实际更新”的情况，您还需要在开始时检查您的查询是否影响了任何行：
set nocount on; -- this must be the first statement!
if not exists (select 1 from inserted) and not exists (select 1 from deleted)
return;
回答by Shyam Sharma
SET NOCOUNT ON;
declare @countTemp int
select @countTemp = Count (*) from (
select City,PostCode,Street,CountryId,Address1 from Deleted
union
select City,PostCode,Street,CountryId,Address1 from Inserted
) tempTable
IF ( @countTemp > 1 )
Begin
-- Your Code goes Here
End
-- if any of these "City,PostCode,Street,CountryId,Address1" got updated then trigger
-- will work in " IF ( @countTemp > 1 ) " Code)
回答by Sirentec
This worked for me
这对我有用
DECLARE @LongDescDirty bit = 0
Declare @old varchar(4000) = (SELECT LongDescription from deleted)
Declare @new varchar(4000) = (SELECT LongDescription from inserted)
if (@old <> @new)
BEGIN
SET @LongDescDirty = 1
END
Update table
Set LongDescUpdated = @LongDescUpdated
.....
.....
