Ответ 1

Предупреждение о видимости: не используйте самый высокий голос. Это даст неверные значения. Продолжайте читать, как это неправильно.

Учитывая, что kludge необходим для работы 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. Это не так плохо, поскольку сама Entity Framework использует этот же хак!

Надеюсь, 2012 2014 2016 2018 будет иметь лучшую реализацию.

Обновление: использование OUTPUT вредно

Проблема, с которой мы начали, заключалась в попытке использовать предложение OUTPUT для получения значений "после" в таблице:

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

Это затем достигает общеизвестного ограничения (ошибка не исправляется) в SQL Server:

В целевой таблице "BatchReports" инструкции DML не может быть никаких включенных триггеров, если инструкция содержит предложение OUTPUT без предложения INTO

Обходной путь № 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 времени, RowVersion мы возвращаем RowVersion, не является значением отметки времени, существовавшей после выполнения ОБНОВЛЕНИЯ:

• возвращенная метка времени: 0x0000000001B71692
• фактическая timestamp: 0x0000000001B71693

Это потому, что значения OUTPUT в нашей таблице не являются значениями, которые были в конце инструкции UPDATE:

• Оператор UPDATE начинается
  • изменить строку
  • timestamp обновлена
  • получить новую метку времени
  • триггеры
    • изменить строку
    • timestamp обновлена
• ОБНОВЛЕНИЕ выражение завершено

Это означает:

• Мы не получаем отметку времени, поскольку она существует в конце инструкции UPDATE
• мы получаем метку времени, как это было в неопределенной середине инструкции UPDATE
• мы не получаем правильную метку времени

То же самое верно для любого триггера, который изменяет любое значение в строке. ВЫХОД не будет ВЫХОДИТЬ значение на конец ОБНОВЛЕНИЯ.

Это означает, что вы не доверяете OUTPUT возвращать какие-либо правильные значения

Эта болезненная реальность задокументирована в BOL:

Столбцы, возвращаемые из OUTPUT, отражают данные в том виде, в каком они есть после выполнения оператора INSERT, UPDATE или DELETE, но до выполнения триггеров.

Как Entity Framework решил это?

.NET Entity Framework использует Rowversion для оптимистического параллелизма. EF зависит от знания значения timestamp после ее выдачи ОБНОВЛЕНИЯ.

Поскольку вы не можете использовать OUTPUT для каких-либо важных данных, Microsoft Entity Framework использует тот же обходной путь, что и я:

Обходной путь № 3 - Финал

Чтобы получить значения после, 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.

Как насчет вставки

Делайте то, что делает 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

Ответ 2

Чтобы обойти это ограничение, вам нужно OUTPUT INTO... что-то. например, объявить переменную промежуточной таблицы как цель, а затем SELECT из этого.

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

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

SELECT *
FROM   @T 

Как указано в другом ответе, если ваш триггер выполняет запись обратно в строки, измененные самим оператором UPDATE таким образом, что он влияет на столбцы, для которых вы используете OUTPUT -ing, тогда вы можете не найти полезных результатов, но это только подмножество триггеров. Описанный выше метод отлично работает в других случаях, таких как запись триггеров в другие таблицы для целей аудита или возврат вставленных значений идентификаторов, даже если исходная строка записана обратно в триггер.

Ответ 3

Зачем помещать все необходимые столбцы в табличную переменную? Нам просто нужен первичный ключ, и мы можем прочитать все данные после ОБНОВЛЕНИЯ. Когда вы используете транзакцию, гонки не происходит:

DECLARE @t TABLE (ID INT PRIMARY KEY);

BEGIN TRAN;

UPDATE BatchReports SET 
    IsProcessed = 1
OUTPUT inserted.ID INTO @t(ID)
WHERE BatchReports.BatchReportGUID = @someGuid;

SELECT b.* 
FROM @t t JOIN BatchReports b ON t.ID = b.ID;

COMMIT;