Cannot use UPDATE with OUTPUT clause when a trigger is on the table
Visibility Warning: Don't the other answer. It will give incorrect values. Read on for why it's wrong.
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
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 2019 2020 will have a better implementation.
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:
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:
The target table 'BatchReports' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause
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
:
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
- actual timestamp:
0x0000000001B71693
That is because the values OUTPUT
into our table are not the values as they were at the end of the UPDATE statement:
- UPDATE statement starting
- modifies row
- timestamp is updated (e.g. 2 → 3)
- OUTPUT retrieves new timestamp (i.e. 3)
- trigger runs
- modifies row again
- timestamp is updated (e.g. 3 → 4)
- modifies row again
- modifies row
- UPDATE statement complete
- OUTPUT returns 3 (the wrong value)
This means:
- We do not get the timestamp as it exists at the end of the UPDATE statement (4)
- Instead we get the timestamp as it was in the indeterminate middle of the UPDATE statement (3)
- 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 cannot trust OUTPUT to return any correct values ever.
This painful reality is documented in the 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.
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.
Since you cannot use OUTPUT
for any important data, Microsoft's Entity Framework uses the same workaround that I do:
Workaround #3 - Final - Do not use OUTPUT clause
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.
What about INSERTs
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
Again, they use a SELECT
statement to read the row, rather than placing any trust in the OUTPUT clause.
Cannot use OUTPUT clause with INTO clause when using Triggers to update records in SQL, when adding a record in FileMaker
This has been reported as a bug: http://help.filemaker.com/app/answers/detail/a_id/7870/~/external-sql-data-sources-(ess)%3A-unable-to-write-to-a-table-with-triggers-on
It does not look like it was resolved.
Strange error when using output clause on an update statement?
This
http://msdn.microsoft.com/en-au/library/ms177564.aspx
says this
If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.
Related Topics
Does Sparksql Support Subquery
Generate Script in SQL Server Management Studio
MySQL Returning the Top 5 of Each Category
How to Pivot Rows into Columns (Custom Pivoting)
Access to Result Sets from Within Stored Procedures Transact-SQL SQL Server
Oracle: Loading a Large Xml File
SQL Server: Drop Table Cascade Equivalent
Using Tuples in SQL "In" Clause
Cannot Use Update with Output Clause When a Trigger Is on the Table
SQL - How to Select a Row Having a Column with Max Value
Script to Save Varbinary Data to Disk
Aggregate Function in SQL Where-Clause
Combining the Results of Two SQL Queries as Separate Columns
Find Closest Date in SQL Server