当触发器在表上时，不能使用UPDATE with OUTPUT子句

I'm performing an UPDATE with OUTPUT query:

我正在用输出查询执行更新:

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 UPDATE statement will get the error 334:

这个说法很好;直到在表上定义了触发器。然后我的更新语句会得到错误334:

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

如果语句包含一个不包含子句的输出子句，则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 UPDATE to so that it works?

因此，让我问自己一个问题:我应该将我的更新修改为什么，以便它能够工作?

See also

• UPDATE with OUTPUT clause – Triggers – and SQLMoreResults
• 使用OUTPUT子句—触发器—和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
• 语句包含没有INTO子句错误的输出子句

2 个解决方案

#1

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

要解决这个限制，你需要将输出到……一些东西。例如，将中间表变量声明为目标，然后从中进行选择。

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 UPDATE statement 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语句本身修改的行，使其影响到您要输出的列，那么您可能会发现结果没有用处，但这只是触发器的一个子集。上述技术在其他情况下工作得很好，例如为了审计目的记录到其他表的触发器，或者返回插入的标识值，即使原始行被写入触发器中。

#2

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 UPDATE with OUTPUT work in SQL Server 2008 R2, i changed my query from:

由于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 itself uses this very same hack!

基本上，我停止使用输出。这并不像实体框架本身使用的一样糟糕!

Hopefully 2012 2014 2016 2018 will have a better implementation.

希望2012年2014年2016年2018年能有更好的实施。

---

Update: using OUTPUT is harmful

The problem we started with was trying to use the OUTPUT clause 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中访问众所周知的限制(won't-fix bug):

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

如果语句包含一个不包含子句的输出子句，则DML语句的目标表“BatchReports”不能有任何启用的触发器。

Workaround Attempt #1

So we try something where we will use an intermediate TABLE variable to hold the OUTPUT results:

我们尝试使用中间表变量来保存输出结果:

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 timestamp into the table (even a temporary table variable).

但是失败了，因为不允许向表中插入时间戳(甚至是临时表变量)。

Workaround Attempt #2

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

我们秘密地知道时间戳实际上是一个64位(即8字节)无符号整数。我们可以将临时表定义改为使用二进制(8)而不是时间戳:

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 RowVersion we return is not the value of the timestamp as it existed after the UPDATE completed:

我们返回的时间戳行版本不是更新完成后的时间戳值:

• returned timestamp: 0x0000000001B71692
• 返回时间戳:0 x0000000001b71692
• actual timestamp: 0x0000000001B71693
• 实际时间戳:0 x0000000001b71693

That is because the values OUTPUT into our table are not the values as they were at the end of the UPDATE statement:

这是因为我们表中输出的值不是UPDATE语句末尾的值:

• UPDATE statement starting
  • modify row
  • 修改的行
  • timestamp is updated
  • 更新时间戳
  • retrieve new timestamp
  • 检索新的时间戳
  • trigger runs
    • modify row
    • 修改的行
    • timestamp is updated
    • 更新时间戳
  • 触发器运行修改行时间戳被更新
• 更新语句开始修改行时间戳是更新的检索新的时间戳触发器运行修改行时间戳更新。
• UPDATE statement complete
• 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
• 我们获得时间戳，因为它位于UPDATE语句的不确定中间
• we do not get the correct timestamp
• 我们没有得到正确的时间戳。

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

任何修改行的任何值的触发器都是如此。在更新结束时，输出将不会输出值。

This means you not trust OUTPUT to return any correct values

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

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.

从输出返回的列反映数据，就像在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 timestamp as it after they issue an UPDATE.

. net实体框架使用rowversion实现乐观并发。EF依赖于在发布更新后知道时间戳的值。

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

由于您不能对任何重要数据使用输出，微软的实体框架使用与我相同的解决方案:

Workaround #3 - Final

In order to retrieve the after values, 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.

不要使用输出。

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

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