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

Question

我正在执行 UPDATE 与 OUTPUT 查询：

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

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

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

现在 SQL Server 团队在一篇博文中解释了这个问题—— 带有 OUTPUT 子句的 UPDATE——触发器——和 SQLMoreResults ：

错误消息是不言自明的

他们还给出了解决方案：

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

除了我无法对整个博客文章做出判断。

所以让我问我的问题：我应该如何更改我的 UPDATE 以使其正常工作？

也可以看看

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

原文由 Ian Boyd 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

能见度警告：不要 其他答案。它会给出不正确的值。继续阅读为什么它是错误的。

---

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

 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！

希望20122014201620182019 2020 会有更好的实施。

---

更新：使用 OUTPUT 是有害的

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

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

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

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

解决方法尝试 #1

所以我们尝试一些我们将使用中间 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

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

解决方法尝试 #2

我们偷偷知道 timestamp 实际上是一个 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

这行得通， _除了值是错误的_。

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

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

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

• UPDATE 语句开始
  • 修改行
    • 时间戳已更新 （例如 2 → 3）
  • OUTPUT 检索新的时间戳 （即 3）
  • 触发运行
    • 再次修改行
      • 时间戳已更新 （例如 3 → 4）
• UPDATE 语句完成
• OUTPUT 返回 3 （错误值）

这表示：

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

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

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

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

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

Entity Framework 是如何解决的？

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

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

解决方法 #3 - 最终 - 不要使用 OUTPUT 子句

为了检索 after 值，实体框架发出：

 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 可以做的最好的事情。

那么 INSERT 呢？

做实体框架做的事情：

 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 子句。

原文由 Ian Boyd 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

要解决此限制，您需要 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

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

原文由 Martin Smith 发布，翻译遵循 CC BY-SA 3.0 许可协议