当触发器在表上时,不能将 UPDATE 与 OUTPUT 子句一起使用 [英] Cannot use UPDATE with OUTPUT clause when a trigger is on the table
问题描述
我正在使用 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:
如果 DML 语句的目标表 'BatchReports' 包含一个没有 INTO 子句的 OUTPUT 子句,则该语句不能有任何已启用的触发器
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 子句的 UPDATE – 触发器 – 和 SQLMoreResults
- 为什么 MERGE 语句的目标表不允许启用规则?
- 语句包含一个没有 INTO 子句错误的 OUTPUT 子句一个>
推荐答案
可见性警告:不要 其他答案.它会给出不正确的值.继续阅读,了解错误的原因.
Visibility Warning: Don't the other answer. It will give incorrect values. Read on for why it's wrong.
考虑到在 SQL Server 2008 R2 中使用 OUTPUT
使 UPDATE
工作所需的杂项,我将查询从:
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:
如果 DML 语句的目标表 'BatchReports' 包含一个没有 INTO 子句的 OUTPUT 子句,则该语句不能有任何已启用的触发器
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
不会输出更新结束时的值.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 实体框架将 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 的实体框架使用与我相同的解决方法:Since you cannot use
OUTPUT
for any important data, Microsoft's Entity Framework uses the same workaround that I do:为了检索 after 值,实体框架问题:
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.
做实体框架要做的事情:
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
同样,他们使用
SELECT
语句来读取行,而不是将任何信任置于 OUTPUT 子句中.Again, they use a
SELECT
statement to read the row, rather than placing any trust in the OUTPUT clause.这篇关于当触发器在表上时,不能将 UPDATE 与 OUTPUT 子句一起使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- 修改行