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

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我正在使用 OUTPUT 查询执行 UPDATE:

I'm performing an UPDATE with OUTPUT query:

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

这个说法很好；直到在表上定义触发器.然后我的 UPDATE 语句将得到错误 334:

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

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

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

现在 SQL Server 团队在一篇博文中解释了这个问题 -- UPDATE 与 OUTPUT 子句 - 触发器 - 和 SQLMoreResults:

Now this problem is explained in a blog post by the SQL Server team -- UPDATE with OUTPUT clause – Triggers – and SQLMoreResults:

错误信息一目了然

他们也给出了解决方案:

And they also give solutions:

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

The application was changed to utilize the INTO clause

除非我无法对整篇博文做出判断.

Except I cannot make head nor tail of the entirety of the blog post.

所以让我问我的问题:我应该将我的 UPDATE 更改为什么才能正常工作?

So let me ask my question: What should I change my UPDATE to so that it works?

  • 使用 OUTPUT 子句进行更新 - 触发器 - 和 SQLMoreResults
  • 为什么MERGE语句的目标表不允许启用规则?
  • 语句包含一个没有INTO子句错误的OUTPUT子句

推荐答案

可见性警告:不要 其他答案.它会给出不正确的值.继续阅读，了解错误的原因.

Visibility Warning: Don't the other answer. It will give incorrect values. Read on for why it's wrong.

考虑到使 UPDATE 和 OUTPUT 在 SQL Server 2008 R2 中工作所需的复杂性，我将查询更改为:

Given the kludge needed to make UPDATE with OUTPUT work in SQL Server 2008 R2, I changed my query from:

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

到:

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

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

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

Basically I stopped using OUTPUT. This isn't so bad as Entity Framework itself uses this very same hack!

希望 2012 2014 2016 2018 2019 2020 年会有更好的实施.

Hopefully 2012 2014 2016 2018 2019 2020 will have a better implementation.

我们开始的问题是尝试使用 OUTPUT 子句来检索表中的 after" 值:

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

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

然后达到 SQL Server 中众所周知的限制(不会修复"错误):

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

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

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

解决方法尝试 #1

所以我们尝试使用中间 TABLE 变量来保存 OUTPUT 结果:

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

除非因为不允许您将 timestamp 插入表(甚至是临时表变量)而失败.

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

我们偷偷知道 timestamp 实际上是一个 64 位(又名 8 字节)的无符号整数.我们可以将临时表定义更改为使用 binary(8) 而不是 timestamp:

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:

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.

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

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

  • 返回的时间戳:0x0000000001B71692
  • 实际时间戳:0x0000000001B71693

这是因为我们表中的 OUTPUT 值不是它们在 UPDATE 语句末尾的值:

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

  • UPDATE 语句开始
    • 修改行
      • 时间戳已更新(例如 2 → 3)
      • 再次修改行
        • 时间戳已更新(例如 3 → 4)

        这意味着:

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

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

        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.

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

        BOL 中记录了这一痛苦的现实:

        This painful reality is documented in the BOL:

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

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

        Entity Framework 是如何解决的?

        .NET Entity Framework 使用 rowversion 实现乐观并发.EF 依赖于知道 timestamp 的值，因为它在发出 UPDATE 后存在.

        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 exists after they issue an UPDATE.

        由于您不能将 OUTPUT 用于任何重要数据，Microsoft 的 Entity Framework 使用与我相同的解决方法:

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

        为了检索 after 值，Entity Framework 问题:

        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

        不要使用 OUTPUT.

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

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

        做实体框架做的事情:

        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

        同样，他们使用 SELECT 语句来读取行，而不是信任 OUTPUT 子句.

        Again, they use a SELECT statement to read the row, rather than placing any trust in the OUTPUT clause.

相关文章