Ian Boyd Ian Boyd - 1 month ago 11
SQL Question

Cannot use UPDATE with OUTPUT clause when a trigger is on the table

I'm performing an

UPDATE
with
OUTPUT
query:

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


This statement is well and fine; until a trigger is defined on the table. Then my
UPDATE
statement will get the error 334:


The target table 'BatchReports' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause


Now this problem is explained in a blog post by the SQL Server team:


The error message is self-explanatory


And they also give solutions:


The application was changed to utilize the INTO clause


Except I cannot make heads or tails of the entirety of the blog post.

So let me ask my question: What should i change my
UPDATE
to so that it works?

See also




Answer

To work around this restriction you need to OUTPUT INTO ... something. e.g. declare an intermediary table variable to be the target then SELECT from that.

DECLARE @T TABLE (
  BatchFileXml    XML,
  ResponseFileXml XML,
  ProcessedDate   DATE )

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

SELECT *
FROM   @T 
Comments