[SQL] 트리거 오류 : 현재 트랜잭션은 커밋 할 수 없습니다 및 운영을 지원하지 수있는 로그 파일에 쓰기

트리거 오류 : 현재 트랜잭션은 커밋 할 수 없습니다 및 운영을 지원하지 수있는 로그 파일에 쓰기

내가 SQL Server에서 다음과 같은 오류 메시지가 무엇입니까 그래서 sp_SomeProc 잘못된 SQL 문을 실행하려고 할 때. 나는 오류가 발생합니다 :

The current transaction cannot be committed and cannot support operations that write to the log file. 

내가 잘못을하고있는 무슨에 어떤 아이디어? (이것이 내가 문제가 매우 기쁘게 모방으로 만든 단지 샘플입니다에는 "왜 이러는거야?", 등 "이 보안 의미를 가지고"없음)

그래서 내 테이블 외모가 좋아 :

CREATE TABLE tSOMETABLE
(  
    RecID INT NOT NULL IDENTITY(1,1)
    Val VARCHAR(20),
CONSTRAINT [PK_tSOMETABLE] PRIMARY KEY CLUSTERED 
(
    RecID ASC
)
)

그래서 내 트리거에서 내가 가진 :

CREATE TRIGGER [dbo].[TR_tSOMETABLE_INSERT]     
    ON [dbo].[tSOMETABLE]   
    FOR INSERT  
AS      
SET NOCOUNT ON  
BEGIN   
         BEGIN
            SELECT * INTO #temp FROM INSERTED

            WHILE EXISTS (SELECT 1 FROM #temp)
            BEGIN
                DECLARE @RecID INT      
                SELECT @RecID = RecID
                FROM #temp t
                EXEC dbo.sp_SomeProc @EventType = 'ON INSERT', @RecID = @RecID
                DELETE #temp WHERE @RecID = RecID
            END         
        END   
END

이제 sp_SomeProc의 코드는 다음과 같습니다

CREATE PROC sp_SomeProc 
(
    @EventType VARCHAR(50),
    @RecID INT,
    @Debug BIT = 0
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @ProcTable TABLE 
    (
        RecID INT NOT NULL IDENTITY(1,1),
        Cmd VARCHAR(MAX)
    )

    INSERT INTO @ProcTable(Cmd)
      SELECT 'EXEC sp_who'
      UNION
      SELECT 'EXEC sp_SomeStoredProcThatDoesntExist'


    DECLARE  @RecID INT  
    SELECT @RecID = MIN(RecID) FROM @ProcTable
    WHILE @RecID IS NOT NULL
    BEGIN  
        DECLARE @sql VARCHAR(MAX)
        SELECT @sql = cmd FROM @ProcTable WHERE RecID = @RecID
        IF @Debug = 1
            PRINT @sql
        ELSE
            BEGIN
                BEGIN TRY      
                    EXEC(@sql)
                END TRY
                BEGIN CATCH
                    DECLARE @Msg VARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState int, @ErrorProcedure nvarchar(256), @ErrorLine int, @ErrorMessage nvarchar(MAX)
                    SELECT @Msg = 'Failed While Executing: ' + @sql  
                    SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorProcedure = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE(), @ErrorMessage = ERROR_MESSAGE()
                    -- DO SOME MORE STUFF HERE AND THEN ...
                    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
                END CATCH 
            END
        SELECT @RecID = MIN(RecID) FROM @ProcTable WHERE RecID > @RecID
    END  
END

그래서 시험에 내가 시도 :

INSERT INTO tSOMETABLE(Val)
SELECT 'Hello'

해결법

1. ==============================

   1.당신이 트랜잭션의 try / catch 블록의 내부를 사용할 때이 오류가 발생합니다. 의 사소한 예를 살펴 보자 :

   당신이 트랜잭션의 try / catch 블록의 내부를 사용할 때이 오류가 발생합니다. 의 사소한 예를 살펴 보자 :

   SET XACT_ABORT ON
   
   IF object_id('tempdb..#t') IS NOT NULL
       DROP TABLE #t
   CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
   
   BEGIN TRAN
       INSERT INTO #t (i) VALUES (1)
       INSERT INTO #t (i) VALUES (2)
       INSERT INTO #t (i) VALUES (3)
       INSERT INTO #t (i) VALUES (1) -- dup key error, XACT_ABORT kills the batch
       INSERT INTO #t (i) VALUES (4) 
   
   COMMIT  TRAN
   SELECT * FROM #t
   

   네 번째 삽입 오류가 발생하면 일괄 처리가 종료되고 트랜잭션이 롤백됩니다. 아니 지금까지 놀라게한다.

   이제 try / catch 블록과 그 오류를 처리하려고 시도하자 :

   SET XACT_ABORT ON
   IF object_id('tempdb..#t') IS NOT NULL
       DROP TABLE #t
   CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
   
   BEGIN TRAN
       INSERT INTO #t (i) VALUES (1)
       INSERT INTO #t (i) VALUES (2)
       BEGIN TRY
           INSERT INTO #t (i) VALUES (3)
           INSERT INTO #t (i) VALUES (1) -- dup key error
       END TRY
       BEGIN CATCH
           SELECT ERROR_MESSAGE()
       END CATCH  
       INSERT INTO #t (i) VALUES (4)
       /* Error the Current Transaction cannot be committed and 
       cannot support operations that write to the log file. Roll back the transaction. */
   
   COMMIT TRAN
   SELECT * FROM #t
   

   우리는 중복 키 오류를 잡았지만, 그렇지 않으면, 우리는 더 낫지 길 이죠. 우리의 배치는 여전히 종료됩니다, 우리의 거래는 여전히 롤백됩니다. 그 이유는 실제로 매우 간단하다 :

   TRY / CATCH 블록 거래에 영향을 미치지 않습니다.

   때문에 XACT_ABORT ON 중복 키 오류가 발생하는 순간 데, 트랜잭션은 운명을 정한다. 그것은을 위해 이루어집니다. 그것은 치명적으로 부상하고있다. 그것은 마음을 통해 쐈이었다 ... 그리고 오류가 비난에 있습니다. TRY / CATCH은 SQL 서버 ... 나쁜 이름을 제공합니다. (죄송합니다, 저항 할 수 없었다)

   즉, 커밋하지 않습니다 항상 롤백됩니다. 할 수있는 모든 try / catch 블록은 시체의 가을을 깰 수 있습니다. 우리는 우리의 트랜잭션이 커미터이 있는지 여부를 확인하기 위해 XACT_STATE () 함수를 사용할 수 있습니다. 그렇지 않은 경우, 유일한 옵션은 트랜잭션을 롤백하는 것입니다.

   SET XACT_ABORT ON -- Try with it OFF as well.
   IF object_id('tempdb..#t') IS NOT NULL
       DROP TABLE #t
   CREATE TABLE #t (i INT NOT NULL PRIMARY KEY)
   
   BEGIN TRAN
       INSERT INTO #t (i) VALUES (1)
       INSERT INTO #t (i) VALUES (2)
   
       SAVE TRANSACTION Save1
       BEGIN TRY
           INSERT INTO #t (i) VALUES (3)
           INSERT INTO #t (i) VALUES (1) -- dup key error
       END TRY
       BEGIN CATCH
           SELECT ERROR_MESSAGE()
           IF XACT_STATE() = -1 -- Transaction is doomed, Rollback everything.
               ROLLBACK TRAN
           IF XACT_STATE() = 1 --Transaction is commitable, we can rollback to a save point
               ROLLBACK TRAN Save1
       END CATCH  
       INSERT INTO #t (i) VALUES (4)
   
   IF @@TRANCOUNT > 0
       COMMIT TRAN
   SELECT * FROM #t
   

   트리거는 항상 당신이 그들 내부 TRY / CATCH를 사용하여 피할 수 그렇다면, 트랜잭션의 컨텍스트 내에서 실행 상황이 훨씬 간단하다.

   문제를 해결하기 위해, 발동 저장된 CLR은 동적 SQL을 실행하기 위해 별도의 연결에 SQL Server에 다시 연결할 수 있습니다. 당신은 새로운 트랜잭션의 코드를 실행할 수있는 능력을 확보하고 로직을 처리 오류가 C #으로 이해하기 쉽고 쓰기 쉬운 모두이다.

from https://stackoverflow.com/questions/15980318/trigger-error-the-current-transaction-cannot-be-committed-and-cannot-support-op by cc-by-sa and MIT license