Ausgabe
Ich führe eine UPDATE
with – OUTPUT
Abfrage durch:
UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid
Diese Aussage ist gut und schön; bis ein Trigger in der Tabelle definiert ist. Dann wird meine UPDATE
Anweisung den Fehler 334 erhalten :
Die Zieltabelle ‘BatchReports’ der DML-Anweisung kann keine aktivierten Trigger haben, wenn die Anweisung eine OUTPUT-Klausel ohne INTO-Klausel enthält
Nun wird dieses Problem in einem Blogbeitrag des SQL Server-Teams – UPDATE mit OUTPUT-Klausel – Triggers – und SQLMoreResults – erklärt :
Die Fehlermeldung ist selbsterklärend
Und sie geben auch Lösungen:
Die Anwendung wurde geändert, um die INTO-Klausel zu verwenden
Nur kann ich aus dem gesamten Blog-Beitrag weder Kopf noch Schwanz machen.
Lassen Sie mich also meine Frage stellen: Was muss ich ändern UPDATE
, damit es funktioniert?
Siehe auch
- UPDATE mit OUTPUT-Klausel – Trigger – und SQLMoreResults
- Warum darf die Zieltabelle einer MERGE-Anweisung keine aktivierten Regeln haben?
- Die Anweisung enthält eine OUTPUT-Klausel ohne INTO-Klauselfehler
Lösung
Sichtbarkeitswarnung : Nicht die andere Antwort . Es werden falsche Werte ausgegeben. Lesen Sie weiter, warum es falsch ist.
Angesichts des Fehlers, der UPDATE
mit der OUTPUT
Arbeit in SQL Server 2008 R2 gemacht werden musste, änderte ich meine Abfrage von:
UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid
zu:
SELECT BatchFileXml, ResponseFileXml, ProcessedDate FROM BatchReports
WHERE BatchReports.BatchReportGUID = @someGuid
UPDATE BatchReports
SET IsProcessed = 1
WHERE BatchReports.BatchReportGUID = @someGuid
Grundsätzlich habe ich aufgehört zu verwenden OUTPUT
. Das ist nicht so schlimm, da Entity Framework selbst denselben Hack verwendet!
Hoffentlich wird 2012 2014 2016 2018 2019 2020 eine bessere Umsetzung haben.
Update: Die Verwendung von OUTPUT ist schädlich
Das Problem, mit dem wir begonnen haben, war der Versuch, die Klausel zu verwenden , um die „after“ -Werte in einer Tabelle OUTPUT
abzurufen :
UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
WHERE BatchReports.BatchReportGUID = @someGuid
Das trifft dann auf die bekannte Einschränkung ( “won’t-fix” bug) in SQL Server:
Die Zieltabelle ‘BatchReports’ der DML-Anweisung kann keine aktivierten Trigger haben, wenn die Anweisung eine OUTPUT-Klausel ohne INTO-Klausel enthält
Workaround-Versuch Nr. 1
Also versuchen wir etwas, wo wir eine Zwischenvariable verwenden TABLE
, um die OUTPUT
Ergebnisse zu speichern:
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
Nur dass das fehlschlägt, weil Sie kein in die Tabelle einfügen dürfen timestamp
(auch nicht eine temporäre Tabellenvariable).
Workaround-Versuch Nr. 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
Auch hier verwenden sie eine SELECT
Anweisung, um die Zeile zu lesen, anstatt der OUTPUT-Klausel zu vertrauen.
Beantwortet von – Ian Boyd
Antwort geprüft von – David Marino (FixError Volunteer)