8

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
Adi
  • 123
  • 1
  • 2
  • 6

4 Answers4

19

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:

Kenny Evitt
  • 9,291
  • 5
  • 65
  • 93
ERIC DE FREITAS MATOS
  • 191
  • 1
  • 3
  • 1
    Are you sure the default value is on? All the information I can find suggests that the default value is off – Phil Hale May 14 '13 at 10:25
  • From the docs, for SQL Server 2008+, "OFF is the default setting." – Kenny Evitt May 25 '17 at 19:46
  • 1
    Optionally, I've found that I could leave this out of the Trigger, but turn it off in a Sproc called by the Trigger (where I already have error handling) and it will work there instead. Amazing, the Sproc handles the error when run from Management Studio, but if a trigger calls it, it will still bubble up without this set to OFF. Thank you! – MikeTeeVee Jun 30 '18 at 21:27
  • 1
    I assume you have to turn it ON at the end of the trigger so other queries will still abort if they run after this trigger. – Martin Sep 21 '22 at 15:06
1

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thanks for the suggestion. I actually have a service broker mechanism in place, thats the non critical part that I am talking about. I don't want the main transaction to rollback incase the service broker mechanism fails. I'll stick with the current implementation only. Thanks for your help. – Adi Apr 03 '12 at 03:48
  • regarding your last statement you shouldnt do..... does that mean we should not have any business logic code directly or indirectly inside the trigger? if so, doesnt this make life very difficult ?? – Jami Jun 29 '15 at 11:13
  • 1
    @Sahand - it depends - is this "business logic" something that should (optionally), cause the *original* transaction to fail? If so, then it should be in the trigger (or better, expressed more directly through other DRI such as foreign keys or check constraints). In the alternative, if the original operation should *always* succeed and this "business logic" is separate then I'd go with what I've said in this answer and use some form of queueing to decouple the processing. The code that *reads* the queued changes can be as complex as you want. – Damien_The_Unbeliever Jun 29 '15 at 12:08
  • What if that insert fails? – Daniel Santos Apr 20 '16 at 18:12
  • @Daniel - there can't be many reasons for a local insert to fail that aren't an indication that the entire database is hosed. E.g. transaction logs are full and cannot grow. – Damien_The_Unbeliever Apr 21 '16 at 06:39
0

This is a very similar question to Why try catch does not suppress exception in trigger

Also see the answer here T-SQL try catch transaction in trigger

Community
  • 1
  • 1
MrGoodbyte
  • 69
  • 5
0

I don’t think you can use savepoints inside a trigger. I mean, you can but I googled about it and I saw a few people saying that they don’t work. If you replace your “save transaction” for a begin transaction, it compiles. Of course it is not necessary because you have the outer transaction control and the inner rollback would rollback everything.

Diego
  • 34,802
  • 21
  • 91
  • 134