Ausgabe

Ich führe eine UPDATEwith – OUTPUTAbfrage 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 UPDATEAnweisung 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 UPDATEmit der OUTPUTArbeit 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 OUTPUTabzurufen :

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 OUTPUTErgebnisse 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)
• 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 SELECTAnweisung, um die Zeile zu lesen, anstatt der OUTPUT-Klausel zu vertrauen.

Beantwortet von – Ian Boyd

Antwort geprüft von – David Marino (FixError Volunteer)