sql - Trigger Error: The current transaction cannot be committed and cannot support operations that write to the log file

Question

So I am getting the following error message from SQL Server when sp_SomeProc tries to execute an invalid sql statement. I get the error:

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

Any ideas on what I am doing wrong? (this is just a sample that I created to mimic the problem so please no "why are you doing this?", "this has security implications", etc..)

---

So my table looks like:

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

So in my trigger I have:

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

Now the code of sp_SomeProc looks like:

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

So to test I try:

INSERT INTO tSOMETABLE(Val)
SELECT 'Hello'

Answer 1

This error occurs when you use a try/catch block inside of a transaction. Let's consider a trivial example:

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

When the fourth insert causes an error, the batch is terminated and the transaction rolls back. No surprises so far.

Now let's attempt to handle that error with a TRY/CATCH block:

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

We caught the duplicate key error, but otherwise, we're not better off. Our batch still gets terminated, and our transaction still gets rolled back. The reason is actually very simple:

TRY/CATCH blocks don't affect transactions.

Due to having XACT_ABORT ON, the moment the duplicate key error occurs, the transaction is doomed. It's done for. It's been fatally wounded. It's been shot through the heart...and the error's to blame. TRY/CATCH gives SQL Server...a bad name. (sorry, couldn't resist)

In other words, it will NEVER commit and will ALWAYS be rolled back. All a TRY/CATCH block can do is break the fall of the corpse. We can use the XACT_STATE() function to see if our transaction is committable. If it is not, the only option is to roll back the transaction.

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

Triggers always execute within the context of a transaction, so if you can avoid using TRY/CATCH inside them, things are much simpler.

For a solution to your problem, a CLR Stored Proc could connect back to SQL Server in a separate connection to execute the dynamic SQL. You gain the ability to execute the code in a new transaction and the error handling logic is both easy to write and easy to understand in C#.