트리거가 테이블에있을 때 OUTPUT 절과 함께 UPDATE를 사용할 수 없습니다.

트리거가 테이블에있을 때 OUTPUT 절과 함께 UPDATE를 사용할 수 없습니다.

---

UPDATEwith OUTPUT쿼리를 수행하고 있습니다.

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

이 진술은 훌륭하고 훌륭합니다. 트리거가 테이블에 정의 될 때까지. 그런 다음 내 UPDATE진술에 오류 334가 표시됩니다 .

문에 INTO 절이없는 OUTPUT 절이 포함 된 경우 DML 문의 대상 테이블 'BatchReports'에 활성화 된 트리거가있을 수 없습니다.

이제이 문제는 SQL Server 팀의 블로그 게시물에 설명되어 있습니다 .

오류 메시지는 자명합니다.

또한 솔루션을 제공합니다.

INTO 절을 사용하도록 응용 프로그램이 변경되었습니다.

블로그 게시물 전체의 머리 나 꼬리를 만들 수 없다는 점을 제외하고는.

그래서 제 질문을하겠습니다. UPDATE작동 하려면 무엇을 변경해야 합니까?

또한보십시오

• OUTPUT 절을 사용한 UPDATE – 트리거 – 및 SQLMoreResults
• MERGE 문의 대상 테이블이 규칙을 활성화 할 수없는 이유는 무엇입니까?
• 문에 INTO 절 오류없이 OUTPUT 절이 있습니다.

---

이 제한을 해결하려면 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하면 결과가 유용하지 않을 수 있지만 이것은 트리거의 하위 집합 일뿐입니다. 위의 기술은 감사 목적으로 다른 테이블에 기록을 트리거하거나 원래 행이 트리거에 다시 기록 되더라도 삽입 된 ID 값을 반환하는 것과 같은 다른 경우에 잘 작동합니다.

---

가시성 경고 : 가장 많이 득표 한 답변을 사용하지 마십시오 . 잘못된 값을 제공합니다. 그것이 잘못된 방식을 계속 읽으십시오.

---

만드는 데 필요한 kludge을 감안 UPDATE하여 OUTPUTSQL 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에서 잘 알려진 제한 (버그를 수정하지 않음)에 도달합니다.

문에 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

우리는 a 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업데이트가 완료된 후에 존재로 우리의 복귀는 타임 스탬프의 값이 아닙니다 :

• 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
  • modify row
  • timestamp is updated
  • retrieve new timestamp
  • trigger runs
    • modify row
    • timestamp is updated
• UPDATE statement complete

This means:

• We do not get the timestamp as it exists at the end of the UPDATE statement
• we get the timestamp as it was in the indeterminate middle of the UPDATE statement
• 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 not trust OUTPUT to return any correct values

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 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

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

---

Why put all needed columns into table variable? We just need primary key and we can read all data after the UPDATE. There is no race when you use transaction:

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;

ReferenceURL : https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table