Ignoring errors in Trigger

Question

I have a stored procedure which is called inside a trigger on Insert/Update/Delete.

The problem is that there is a certain code block inside this SP which is not critical. Hence I want to ignore any erros arising from this code block.

I inserted this code block inside a TRY CATCH block. But to my surprise I got the following error:

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

Then I tried using SAVE & ROLLBACK TRANSACTION along with TRY CATCH, that too failed with the following error:

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

My server version is: Microsoft SQL Server 2008 (SP2) - 10.0.4279.0 (X64)

Sample DDL:

IF OBJECT_ID('TestTrigger') IS NOT NULL
    DROP TRIGGER TestTrigger
GO
IF OBJECT_ID('TestProcedure') IS NOT NULL
    DROP PROCEDURE TestProcedure
GO
IF OBJECT_ID('TestTable') IS NOT NULL
    DROP TABLE TestTable
GO

CREATE TABLE TestTable (Data VARCHAR(20))
GO

CREATE PROC TestProcedure       
AS      
BEGIN 

    SAVE TRANSACTION Fallback
    BEGIN TRY
        DECLARE @a INT = 1/0
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION Fallback
    END CATCH
END
GO

CREATE TRIGGER TestTrigger
ON TestTable
FOR INSERT, UPDATE, DELETE 
AS
BEGIN
    EXEC TestProcedure   
END
GO

Code to replicate the error:

BEGIN TRANSACTION
INSERT INTO TestTable VALUES('data')
IF @@ERROR > 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION
GO

Answer 1

I was going through the same torment, and I just solved it!!! Just add this single line at the very first step of your TRIGGER and you're going to be fine:

SET XACT_ABORT OFF;

In my case, I'm handling the error feeding a specific table with the batch that caused the error and the error variables from SQL.

Default value for XACT_ABORT is ON, so the entire transaction won't be commited even if you're handling the error inside a TRY CATCH block (just as I'm doing). Setting its value for OFF will cause the transaction to be commited even when an error occurs.

However, I didn't test it when the error is not handled...

For more info:

• SET XACT_ABORT (Transact-SQL) | Microsoft Docs

Answer 2

I'd suggest re-architecting this so that you don't poison the original transaction - maybe have the transaction send a service broker message (or just insert relevant data into some form of queue table), so that the "non-critical" part can take place in a completely independent transaction.

E.g. your trigger becomes:

CREATE TRIGGER TestTrigger
ON TestTable
FOR INSERT, UPDATE, DELETE 
AS
BEGIN
    INSERT INTO QueueTable (Col1,Col2)
    SELECT COALESCE(i.Col1,d.Col1),COALESCE(i.Col2,d.Col2) from inserted i,deleted d
END
GO

You shouldn't do anything inside a trigger that might fail, unless you do want to force the transaction that initiated the trigger action to also fail.