SQL TSQL触发器中的更新函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1249061/
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
Update function in TSQL trigger
提问by David.Chu.ca
I have a question about TSQL function Update. For example, I have a table with a field Name. If I check if the field Name is changed or not in a After Update trigger likes this:
我有一个关于 TSQL 函数更新的问题。例如，我有一个带有字段名称的表。如果我检查更新后触发器中的字段名称是否已更改，则如下所示：
if Update(Name)
Begin
-- process
End
Will the Update still return TRUE even if Name is not changed? The following update statement will update it with the same value:
即使名称未更改，更新是否仍会返回 TRUE？以下更新语句将使用相同的值更新它：
SELECT @v_Name = Name From MyTable Where Id = 1;
Update MyTable Set Name = @v_Name where Id = 1;
If the Update() returns TRUE even the value of Name is not changed, do I have to compare the value in the inserted and deleted virtual tables to find out if the value is really changed?
如果即使 Name 的值没有改变，Update() 也返回 TRUE，我是否必须比较插入和删除的虚拟表中的值来确定该值是否真的改变了？
By the way, the inserted and deleted are virtual tables and they may contain more than one rows of data if more than one rows of data are changed by one TSQL INSERT or UPDATE statement. In case of more than one records, are the count numbers of rows in inserted and deleted virtual tables the same and what is the real meaning of Update(Name) as TRUE? Does it mean that at least one is changed? Or does Update(Name) mean that the field of Name has been set by Update statement regardless if the value is changed?
顺便说一下，insert 和deleted 是虚拟表，如果一个TSQL INSERT 或UPDATE 语句更改了不止一行数据，它们可能包含不止一行数据。如果有多条记录，插入和删除的虚拟表中的行数是否相同，Update(Name)为TRUE的真正含义是什么？这是否意味着至少改变了一个？或者 Update(Name) 是否意味着 Name 字段已由 Update 语句设置，无论值是否更改？
The SQL server I use is Microsoft SQL 2005.
我使用的 SQL 服务器是 Microsoft SQL 2005。
采纳答案by gbn
UPDATE()can be true, even if it's the same value. I would not rely on it personally and would compare values.
UPDATE()可以是真的，即使它是相同的值。我不会个人依赖它，而是会比较值。
Second, DELETEDand INSERTEDhave the same number of rows.
其次，DELETED与INSERTED具有相同的行数。
The Update() function is not per row, but across all rows. Another reason not to use it.
Update() 函数不是每行，而是跨所有行。另一个不使用它的原因。
More here in MSDN, however it's a bit sparse, really.
MSDN 中的更多内容，但确实有点稀疏。
After comment:
评论后：
IF EXISTS (
SELECT
*
FROM
INSERTED I
JOIN
DELETED D ON I.key = D.key
WHERE
D.valuecol <> I.valuecol --watch for NULLs!
)
blah
回答by EricI
Triggers are tricky and you need to think in bulk when you're creating one. A trigger fires once for each UPDATE statement. If that UPDATE statement updates multiple rows, the trigger will still only fire once. The UPDATE() function returns true for a column when that column is included in the UPDATE statement. That function helps to improve the efficiency of triggers by allowing you to sidestep SQL logic when that column isn't even included in the update statement. It doesn't tell you if the value changed for a column in a given row.
触发器很棘手，您在创建触发器时需要进行大量思考。每个 UPDATE 语句触发一次触发器。如果该 UPDATE 语句更新多行，触发器仍然只会触发一次。当该列包含在 UPDATE 语句中时，UPDATE() 函数为该列返回 true。当该列甚至未包含在更新语句中时，该函数允许您回避 SQL 逻辑，从而有助于提高触发器的效率。它不会告诉您给定行中某列的值是否发生了变化。
Here's a sample table...
这是一个示例表...
CREATE TABLE tblSample
(
SampleID INT PRIMARY KEY,
SampleName VARCHAR(10),
SampleNameLastChangedDateTime DATETIME,
Parent_SampleID INT
)
If the following SQL was used against this table:
如果对这个表使用了以下 SQL：
UPDATE tblSample SET SampleName = 'hello'
..and an AFTER INSERT, UPDATE trigger was in effect, this particular SQL statement would always evaluate the UPDATE function as follows...
..和一个 AFTER INSERT, UPDATE 触发器生效，这个特定的 SQL 语句将始终按如下方式评估 UPDATE 函数......
IF UPDATE(SampleName) --aways evaluates to TRUE
IF UPDATE(SampleID) --aways evaluates to FALSE
IF UPDATE(Parent_SampleID) --aways evaluates to FALSE
Note that UPDATE(SampleName) would always be true for this SQL statement, regardless of what the SampleName values were before. It returns true because the UPDATE statement includes the column SampleName in the SET section of that clause and not based on what the values were before or afterward. The UPDATE() function will not determine if the values changed. If you want to do actions based on whether the values are changed you're going to need to use SQL and compare the inserted and deleted rows.
请注意，无论之前的 SampleName 值是什么，UPDATE(SampleName) 对于此 SQL 语句始终为真。它返回 true，因为 UPDATE 语句在该子句的 SET 部分中包含列 SampleName，而不是基于之前或之后的值。UPDATE() 函数不会确定值是否更改。如果您想根据值是否更改执行操作，您将需要使用 SQL 并比较插入和删除的行。
Here's an approach to keeping a last updated column in sync:
这是一种使上次更新的列保持同步的方法：
--/*
IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
GO
--*/
CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample
AFTER INSERT, UPDATE
AS
BEGIN --Trigger
IF UPDATE(SampleName)
BEGIN
UPDATE tblSample SET
SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
WHERE
SampleID IN (SELECT Inserted.SampleID
FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
END
END --Trigger
The logic to determine if the row was updated is in the WHERE clause above. That's the real check you need to do. My logic is using COALESCE to handle NULL values and INSERTS.
确定行是否更新的逻辑在上面的 WHERE 子句中。这才是你真正需要做的检查。我的逻辑是使用 COALESCE 来处理 NULL 值和 INSERTS。
...
WHERE
SampleID IN (SELECT Inserted.SampleID
FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
Note that the IF UPDATE() check is used to help improve the efficiency of the trigger for when the SampleName column is NOT being updated. If a SQL statement updated the Parent_SampleID column for instance then that IF UPDATE(SampleName) check would help sidestep around the more complex logic in that IF statement when it doesn't need to run. Consider using UPDATE() when it's appropriate but not for the wrong reason.
请注意，当 SampleName 列未更新时，IF UPDATE() 检查用于帮助提高触发器的效率。例如，如果 SQL 语句更新了 Parent_SampleID 列，那么 IF UPDATE(SampleName) 检查将有助于在不需要运行时避开该 IF 语句中更复杂的逻辑。考虑在适当的时候使用 UPDATE() 但不是出于错误的原因。
Also realize that depending on your architecture, the UPDATE function may have no use to you. If your code architecture uses a middle-tier that always updates all columns in a row of a table with the values in the business object when the object is saved, the UPDATE() function in a trigger becomes useless. In that case, your code is likely always updating all the columns with every UPDATE statement issued from the middle-tier. That being the case, the UPDATE(columnname) function would always evaluate to true when your business objects are saved because all the column names are always included in the update statements. In that case, it would not be helpful to use UPDATE() in the trigger and would just be extra overhead in that trigger for a majority of the time.
还要意识到，根据您的架构，UPDATE 函数可能对您没有用处。如果您的代码体系结构使用一个中间层，该中间层在保存对象时始终使用业务对象中的值更新表的一行中的所有列，则触发器中的 UPDATE() 函数将变得无用。在这种情况下，您的代码可能总是使用从中间层发出的每个 UPDATE 语句更新所有列。在这种情况下，当您的业务对象被保存时，UPDATE(columnname) 函数将始终评估为 true，因为所有列名称始终包含在更新语句中。在这种情况下，在触发器中使用 UPDATE() 不会有帮助，并且在大多数情况下只会在该触发器中产生额外的开销。
Here's some SQL to play with the trigger above:
这里有一些 SQL 可以使用上面的触发器：
INSERT INTO tblSample
(
SampleID,
SampleName
)
SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'
GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1 One 2010-10-27 14:52:42.567
2 Two 2010-10-27 14:52:42.567
3 Three 2010-10-27 14:52:42.567
*/
GO
INSERT INTO tblSample
(
SampleID,
SampleName
)
SELECT 4, 'Foo'
UNION SELECT 5, 'Five'
GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1 One 2010-10-27 14:52:42.567
2 Two 2010-10-27 14:52:42.567
3 Three 2010-10-27 14:52:42.567
4 Foo 2010-10-27 14:52:42.587
5 Five 2010-10-27 14:52:42.587
*/
GO
UPDATE tblSample SET SampleName = 'Foo'
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1 Foo 2010-10-27 14:52:42.657
2 Foo 2010-10-27 14:52:42.657
3 Foo 2010-10-27 14:52:42.657
4 Foo 2010-10-27 14:52:42.587
5 Foo 2010-10-27 14:52:42.657
*/
GO
UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1 Not Prime 2010-10-27 14:52:42.680
2 Foo 2010-10-27 14:52:42.657
3 Foo 2010-10-27 14:52:42.657
4 Not Prime 2010-10-27 14:52:42.680
5 Foo 2010-10-27 14:52:42.657
*/
--Clean up...
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
DROP TABLE tblSample
User GBN had suggested the following:
用户 GBN 提出了以下建议：
IF EXISTS (
SELECT
*
FROM
INSERTED I
JOIN
DELETED D ON I.key = D.key
WHERE
D.valuecol <> I.valuecol --watch for NULLs!
)
blah
GBN's suggestion of using an IF (EXISTS( ...clause and putting the logic in that IF statement if rows exist that were changed could work. That approach will fire for ALL rows included in the trigger even if only some of the rows were actually changed (which may be appropriate for your solution, but also may not be appropriate if you only want to do something to rows where the values changed.) If you need to do something to rows where an actual change has occurred, you need different logic in your SQL that he provided.
GBN 建议使用 IF (EXISTS( ...clause 并将逻辑放在该 IF 语句中，如果存在已更改的行可以工作。即使只有某些行实际上是更改（这可能适合您的解决方案，但如果您只想对值发生更改的行执行某些操作，则可能也不适合。）如果您需要对发生实际更改的行执行某些操作，则需要不同的逻辑在他提供的 SQL 中。
In my examples above, when the UPDATE tblSample SET SampleName = 'Foo' statement is issued and the fourth row is already 'foo', using GBN's approach to update a "last changed datetime" column would also update the fourth row, which would not be appropriate in this case.
在我上面的示例中，当发出 UPDATE tblSample SET SampleName = 'Foo' 语句并且第四行已经是 'foo' 时，使用 GBN 的方法更新“最后更改的日期时间”列也会更新第四行，这不会在这种情况下是合适的。
回答by Simon Tewsi
I agree the best way to determine if a column value has actually changed (as opposed to being updated with the same value) is to do a comparison of the column values in the deleted and inserted pseudo tables. However, this can be a real pain if you want to check more than a few columns.
我同意确定列值是否实际更改（而不是使用相同值更新）的最佳方法是比较已删除和插入的伪表中的列值。但是，如果您想检查多个列，这可能会很痛苦。
Here's a trick I came across in some code I was maintaining (don't know the original author): Use a UNION and a GROUP BY with a HAVING clause to determine which columns have changed.
这是我在维护的一些代码中遇到的一个技巧（不知道原作者）：使用 UNION 和 GROUP BY 和 HAVING 子句来确定哪些列已更改。
eg, in the trigger, to get the ID's of the rows that have changed:
例如，在触发器中，获取已更改行的 ID：
SELECT SampleID
FROM
(
SELECT SampleID, SampleName
FROM deleted
-- NOTE: UNION, not UNION ALL. UNION by itself removes duplicate
-- rows. UNION ALL includes duplicate rows.
UNION
SELECT SampleID, SampleName
FROM inserted
) x
GROUP BY SampleID
HAVING COUNT(*) > 1
This is too much work when you're only checking if a single column has changed. But if you're checking 10 or 20 columns the UNION method is a lot less work than
当您只检查单个列是否已更改时，这工作量太大。但是，如果您要检查 10 或 20 列，则 UNION 方法的工作量比
WHERE COALESCE(Inserted.Column1, '') <> COALESCE(Deleted.Column1, '')
OR COALESCE(Inserted.Column2, '') <> COALESCE(Deleted.Column2, '')
OR COALESCE(Inserted.Column3, '') <> COALESCE(Deleted.Column3, '')
OR ...
回答by David Coster
I think that the following code is better than the examples above because it focuses on just the columns you want to check in a concise and efficient manner.
我认为下面的代码比上面的例子更好，因为它以简洁有效的方式只关注你想要检查的列。
It determines if a value has changed in only the columns specified. I have not investigated its performance compared with the other solutions but it is working well in my database.
它确定是否仅在指定的列中更改了值。与其他解决方案相比，我没有调查过它的性能，但它在我的数据库中运行良好。
It uses the EXCEPT set operator to return any rows from the left query that are not also found on the right query. This code can be used in INSERT and UPDATE triggers.
它使用 EXCEPT set 运算符返回左侧查询中未在右侧查询中找到的任何行。此代码可用于 INSERT 和 UPDATE 触发器。
The "PrimaryKeyID" column is the primary key of the table (can be multiple columns) and is required to enable matching between the two sets.
“PrimaryKeyID”列是表的主键（可以是多列），需要启用两个集合之间的匹配。
-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT PrimaryKeyID
,Column1
,Column7
,Column10
FROM inserted
EXCEPT
SELECT PrimaryKeyID
,Column1
,Column7
,Column10
FROM deleted ) -- Tests for modifications to fields that we are interested in
BEGIN
-- Put code here that does the work in the trigger
END
If you want to use the changed rows in subsequent trigger logic, I usually put the results of the EXCEPT query into a table variable that can be referenced later on.
如果要在后续触发逻辑中使用更改后的行，我通常会将 EXCEPT 查询的结果放入一个表变量中，以便稍后引用。
I hope this is of interest :-)
我希望这很有趣:-)
回答by nagul
The update trigger will fire on allupdate statements. the impacted rows are available within the trigger in the "inserted" and "deleted" tables. You can compare the old and new values by comparing the PK columns in the two tables (if you have a PK). The actualtable remains unchanged till the trigger finishes execution.
更新触发器将在所有更新语句上触发。受影响的行在触发器中的“插入”和“删除”表中可用。您可以通过比较两个表中的 PK 列来比较旧值和新值（如果您有 PK）。在实际的表保持不变，直到触发完成执行。
