SQL 当触发器在表上时，不能将 UPDATE 与 OUTPUT 子句一起使用

Cannot use UPDATE with OUTPUT clause when a trigger is on the table

提问by Ian Boyd

I'm performing an UPDATEwith OUTPUTquery:

我正在执行UPDATEwithOUTPUT查询：

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid

This statement is well and fine; until a trigger is defined on the table. Then my UPDATEstatement will get the error 334:

这句话说得很好；直到在表上定义触发器。然后我的UPDATE语句会得到错误334：

The target table 'BatchReports' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

如果语句包含没有 INTO 子句的 OUTPUT 子句，则 DML 语句的目标表“BatchReports”不能有任何启用的触发器

Now this problem is explained in a blog post by the SQL Server team:

现在SQL Server 团队在一篇博客文章中解释了这个问题：

The error message is self-explanatory

错误消息是不言自明的

And they also give solutions:

他们还给出了解决方案：

The application was changed to utilize the INTO clause

应用程序已更改为使用 INTO 子句

Except I cannot make heads or tails of the entirety of the blog post.

除了我不能对整个博客文章做出正面或反面。

So let me ask my question: What should i change my UPDATEto so that it works?

所以让我问我的问题：我应该改变什么UPDATE才能使它起作用？

See also

也可以看看

• UPDATE with OUTPUT clause – Triggers – and SQLMoreResults
• Why is the target table of a MERGE statement not allowed to have enabled rules?
• Statement contains an OUTPUT clause without INTO clause error

• 带有 OUTPUT 子句的 UPDATE – 触发器 – 和 SQLMoreResults
• 为什么 MERGE 语句的目标表不允许启用规则？
• 语句包含一个没有 INTO 子句错误的 OUTPUT 子句

采纳答案by Ian Boyd

Visibility Warning: Don't use the highest voted answer. It will give incorrect values. Read on for the way it's wrong.

可见性警告：不要使用投票最高的答案。它会给出不正确的值。请继续阅读它的错误方式。

---

---

Given the kludge needed to make UPDATEwith OUTPUTwork in SQL Server 2008 R2, i changed my query from:

鉴于作出所需的杂牌UPDATE与OUTPUT在SQL Server 2008 R2的工作，我改变了我的查询范围：

UPDATE BatchReports  
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid

to:

到：

SELECT BatchFileXml, ResponseFileXml, ProcessedDate FROM BatchReports
WHERE BatchReports.BatchReportGUID = @someGuid

UPDATE BatchReports
SET IsProcessed = 1
WHERE BatchReports.BatchReportGUID = @someGuid

Basically i stopped using OUTPUT. This isn't so bad as Entity Framework itselfuses this very same hack!

基本上我停止使用OUTPUT. 这还不错，因为Entity Framework 本身使用了同样的 hack！

Hopefully 2012201420162018 will have a better implementation.

希望2012 2014 20162018 会有更好的实施。

---

---

Update: using OUTPUT is harmful

更新：使用 OUTPUT 是有害的

The problem we started with was trying to use the OUTPUTclause to retrieve the "after"values in a table:

我们开始的问题是尝试使用该OUTPUT子句来检索表中的“after”值：

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
WHERE BatchReports.BatchReportGUID = @someGuid

That then hits the well-know limitation (won't-fix bug) in SQL Server:

然后遇到了 SQL Server 中众所周知的限制（不会修复错误）：

The target table 'BatchReports' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

如果语句包含没有 INTO 子句的 OUTPUT 子句，则 DML 语句的目标表“BatchReports”不能有任何启用的触发器

Workaround Attempt #1

解决方法尝试 #1

So we try something where we will use an intermediate TABLEvariable to hold the OUTPUTresults:

因此，我们尝试使用中间TABLE变量来保存OUTPUT结果：

DECLARE @t TABLE (
   LastModifiedDate datetime,
   RowVersion timestamp, 
   BatchReportID int
)

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
INTO @t
WHERE BatchReports.BatchReportGUID = @someGuid

SELECT * FROM @t

Except that fails because you're not allowed to insert a timestampinto the table (even a temporary table variable).

除非失败，因为您不允许将 atimestamp插入表（甚至是临时表变量）。

Workaround Attempt #2

解决方法尝试 #2

We secretly know that a timestampis actually a 64-bit (aka 8 byte) unsigned integer. We can change our temporary table definition to use binary(8)rather than timestamp:

我们秘密地知道 atimestamp实际上是一个 64 位（又名 8 字节）无符号整数。我们可以更改临时表定义以使用binary(8)而不是timestamp：

DECLARE @t TABLE (
   LastModifiedDate datetime,
   RowVersion binary(8), 
   BatchReportID int
)

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
INTO @t
WHERE BatchReports.BatchReportGUID = @someGuid

SELECT * FROM @t

And that works, except that the value are wrong.

这有效，除了值是错误的。

The timestamp RowVersionwe return is not the value of the timestamp as it existed after the UPDATE completed:

RowVersion我们返回的时间戳不是 UPDATE 完成后存在的时间戳的值：

• returned timestamp: 0x0000000001B71692
• actual timestamp: 0x0000000001B71693

• 返回时间戳：0x0000000001B71692
• 实际时间戳：0x0000000001B71693

That is because the values OUTPUTinto our table are notthe values as they were at the end of the UPDATE statement:

那是因为OUTPUT我们表中的值不是UPDATE 语句末尾的值：

• UPDATE statement starting
  • modify row
  • timestamp is updated
  • retrieve new timestamp
  • trigger runs
    • modify row
    • timestamp is updated
• UPDATE statement complete

• UPDATE 语句开始
  • 修改行
  • 时间戳已更新
  • 检索新时间戳
  • 触发运行
    • 修改行
    • 时间戳已更新
• UPDATE 语句完成

This means:

这意味着：

• We do not get the timestamp as it exists at the end of the UPDATE statement
• we get the timestamp as it was in the indeterminate middle of the UPDATE statement
• we do not get the correct timestamp

• 我们没有得到时间戳，因为它存在于 UPDATE 语句的末尾
• 我们得到时间戳，因为它在 UPDATE 语句的不确定中间
• 我们没有得到正确的时间戳

The same is true of anytrigger that modifies anyvalue in the row. The OUTPUT will not OUTPUT the value as of the end of the UPDATE.

修改行中任何值的任何触发器也是如此。OUTPUT 不会输出 UPDATE 结束时的值。

This means you not trust OUTPUT to return any correct values

这意味着您不相信 OUTPUT 会返回任何正确的值

This painful reality is documented in the BOL:

这个痛苦的现实记录在 BOL 中：

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

从 OUTPUT 返回的列反映了 INSERT、UPDATE 或 DELETE 语句完成之后但触发器执行之前的数据。

How did Entity Framework solve it?

实体框架是如何解决的？

The .NET Entity Framework uses rowversion for Optimistic Concurrency. The EF depends on knowing the value of the timestampas it after they issue an UPDATE.

.NET 实体框架将 rowversion 用于乐观并发。EF 依赖于timestamp在发出 UPDATE 后知道 的值。

Since you cannot use OUTPUTfor any important data, Microsoft's Entity Framework uses the same workaround that i do:

由于您不能OUTPUT用于任何重要数据，Microsoft 的实体框架使用与我相同的解决方法：

Workaround #3 - Final

解决方法 #3 - 最终

In order to retrieve the aftervalues, Entity Framework issues:

为了检索后值，实体框架问题：

UPDATE [dbo].[BatchReports]
SET [IsProcessed] = @0
WHERE (([BatchReportGUID] = @1) AND ([RowVersion] = @2))

SELECT [RowVersion], [LastModifiedDate]
FROM [dbo].[BatchReports]
WHERE @@ROWCOUNT > 0 AND [BatchReportGUID] = @1

Don't use OUTPUT.

不要使用OUTPUT.

Yes it suffers from a race condition, but that's the best SQL Server can do.

是的，它受到竞争条件的影响，但这是 SQL Server 能做的最好的事情。

What about INSERTs

INSERT 怎么样

Do what Entity Framework does:

做实体框架所做的：

SET NOCOUNT ON;

DECLARE @generated_keys table([CustomerID] int)

INSERT Customers (FirstName, LastName)
OUTPUT inserted.[CustomerID] INTO @generated_keys
VALUES ('Steve', 'Brown')

SELECT t.[CustomerID], t.[CustomerGuid], t.[RowVersion], t.[CreatedDate]
FROM @generated_keys AS g
   INNER JOIN Customers AS t
   ON g.[CustomerGUID] = t.[CustomerGUID]
WHERE @@ROWCOUNT > 0

回答by Martin Smith

To work around this restriction you need to OUTPUT INTO ...something. e.g. declare an intermediary table variable to be the target then SELECTfrom that.

要解决此限制，您需要做 OUTPUT INTO ...一些事情。例如，将一个中间表变量声明为目标，然后SELECT从中进行。

DECLARE @T TABLE (
  BatchFileXml    XML,
  ResponseFileXml XML,
  ProcessedDate   DATE,
  RowVersion      BINARY(8) )

UPDATE BatchReports
SET    IsProcessed = 1
OUTPUT inserted.BatchFileXml,
       inserted.ResponseFileXml,
       deleted.ProcessedDate,
       inserted.Timestamp
INTO @T
WHERE  BatchReports.BatchReportGUID = @someGuid

SELECT *
FROM   @T 

As cautioned in the other answer if your trigger writes back to the rows modified by the UPDATEstatement itself in such a way that it affects the columns that you are OUTPUT-ing then you may not find the results useful but this is only a subset of triggers. The above technique works fine in other cases, such as triggers recording to other tables for audit purposes, or returning inserted identity values even if the original row is written back to in the trigger.

正如另一个答案中所警告的那样，如果您的触发器以UPDATE某种方式写回由语句本身修改的行，从而影响您正在执行的列，OUTPUT那么您可能不会发现结果有用，但这只是触发器的一个子集。上述技术在其他情况下工作正常，例如触发器记录到其他表以进行审计，或者即使原始行被写回到触发器中也返回插入的标识值。

回答by Marcin Hlibowski

Why put all needed columns into table variable? We just need primary key and we can read all data after the UPDATE. There is no race when you use transaction:

为什么将所有需要的列放入表变量中？我们只需要主键，我们就可以在 UPDATE 之后读取所有数据。使用事务时没有竞争：

DECLARE @t TABLE (ID INT PRIMARY KEY);

BEGIN TRAN;

UPDATE BatchReports SET 
    IsProcessed = 1
OUTPUT inserted.ID INTO @t(ID)
WHERE BatchReports.BatchReportGUID = @someGuid;

SELECT b.* 
FROM @t t JOIN BatchReports b ON t.ID = b.ID;

COMMIT;