質問

私は UPDATE で OUTPUT クエリを実行します。

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

このステートメントは、テーブル上でトリガーが定義されるまでは、よく、うまくいきます。そして、私の UPDATE ステートメントがエラーを発生させます。 334 :

DML 文のターゲット テーブル 'BatchReports' は、文に INTO 句なしの OUTPUT 句が含まれている場合、有効なトリガを持つことができません。

この問題は、SQL Server チームによるブログ投稿で説明されています。 OUTPUT句を使用したUPDATE - トリガー - およびSQLMoreResults :

<ブロッククオート

エラーメッセージは一目瞭然です

そして、解決策も教えてくれます。

<ブロッククオート

INTO句を利用するようにアプリケーションを変更しました。

ただし、ブログの記事全体の頭も尻尾も出ません。

では、質問させてください。私の UPDATE をどのように変更すればよいのでしょうか？

こちらもご覧ください

• OUTPUT句を使用したUPDATE - トリガー - およびSQLMoreResults
• MERGE ステートメントのターゲット テーブルに有効なルールを設定できないのはなぜですか？
• ステートメントには、INTO 節のない OUTPUT 節が含まれていますエラー

どのように解決するのですか？

視認性警告 : を使わないでください。 他の答え . 正しくない値が表示されます。なぜ間違っているのかについては、こちらをご覧ください。

---

を作るために必要な手間を考えると 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 . これは エンティティ・フレームワーク自体 はこれとまったく同じハックを使っているからです!

うまくいけば <ストライク 2014 <ストライク 2016 <ストライク 2018 <ストライク 2019 2020年はより良い実装になります。

---

更新：OUTPUTの使用は有害です

私たちが始めた問題は、このように OUTPUT 節を使用して "after" の値を取得します。

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

これは、よく知られている制限に当たります ( "won't-fix" バグ) にぶつかります。

ステートメントに 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 はUPDATE終了時点の値をOUTPUTしません。

これは、OUTPUTが正しい値を返すことを決して信用できないことを意味します。

この辛い現実はBOLで文書化されています。

OUTPUTから返される列は、INSERT、UPDATE、またはDELETE文が完了した後、トリガが実行される前のデータをそのまま反映しています。

Entity Framework はどのようにそれを解決したのでしょうか？

.NET Entity Frameworkは、Optimistic Concurrencyのためにrowversionを使用しています。EF は、値を知っていることに依存しています。 timestamp の値を知ることに依存しています。

を使用することはできないので OUTPUT を使用することができないため、Microsoft の Entity Framework では、私と同じ回避策を使用しています。

回避策その3 - 最終 - OUTPUT句を使用しない

を取得するために の後に の値を取得するために、Entity Frameworkの問題が発生します。

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についてはどうでしょうか。

Entity Frameworkと同じようにします。

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 ステートメントを使用して行を読み込んでいます。