TSQL: Try-Catch Transaction in Trigger

Question

I am trying to put a try-catch statement inside a trigger using Microsoft Server 2005.

BEGIN TRANSACTION
BEGIN TRY
    --Some More SQL
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END;
END CATCH

The problem is that I don't want the trigger to fail if something is caught by the try-catch block. At the moment, I am getting the error "The transaction ended in the trigger. The batch has been aborted." if the transaction fails. How can I get the trigger to fail gracefully?

---

Additionally, if I remove the transaction, I get the error "Transaction doomed in trigger. Batch has been aborted.".

BEGIN TRY
    --Some More SQL
END TRY
BEGIN CATCH
    return
END CATCH

Is there any way around this?

Answer 1

Use SET XACT_ABORT OFF .When Transact-SQL statement encounter error ,it just raised the error message and the transaction continues processing. The following code is to create the trigger:

Create TRIGGER [dbo].tr_Ins_Table_Master ON [dbo].Table_Master
 AFTER INSERT
AS
BEGIN
set xact_abort off
BEGIN TRY
        --your SQL          
        INSERT INTO Table_Detail
        SELECT MasterID,Name FROM INSERTED

END TRY

BEGIN CATCH     
    select ERROR_MESSAGE()
END CATCH

END

Answer 2

In my experience any error caught in a try catch in a trigger will rollback the entire transaction; you may be able to use a save transaction. I think you need to look at whats happening in "Some more sql" and determine if you can write case / if statements around it to stop the error.

What you may be able todo depending on what you are doing is use a save transaction and capture that in the catch

In your code something like this

SAVE TRANSACTION BeforeUpdate;
BEGIN TRY
        --Some More SQL
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION BeforeUpdate;
        return
END CATCH

Answer 3

Don't rollback in a trigger and there is no need to start a transaction either.

The ROLLBACK TRANSACTION will rollback the original DML trigger and the extra trigger transaction too. So the batch will be aborted

Edit:

I suggest not having a "RETURN" in your catch block and simply allow the code to complete I've never ignored a trapped error in a trigger (but I do use TRY/CATCH in triggers with rollback and raiserror to re-throw) so this is a guess, but the return is probably an abnormal exit condition in the trigger

Also, try to avoid the error condition in the first place. Change the --some more sql to avoid the error. Example, add if exists(... to test for a duplicate first or similar

Answer 4

This demo achieves many of the things asked above. Error messages become optional. The trick that makes it work is in a the nested dynamic execute.

    if object_id('toto')  is not  null drop table toto
    go
    create table toto (i int);
    go
    if object_id('toto2')  is not  null drop table toto2
    go
    create table toto2 (i int);
    go
    create Trigger trtoto
    ON toto
    Instead Of Insert
    as
    Begin
      BEGIN TRY
        set nocount  on
        insert into  toto  values(2)

        declare @sql nvarchar(max) =  'insert into toto2 values(3); select * from ThisTableDoesntexist'

        Exec sp_executeSql N'set xact_abort off; exec (@sql) ', N'@sql nvarchar(max)', @sql

      END TRY

      BEGIN CATCH
        PRINT  'Error on line ' + CAST(ERROR_LINE() AS VARCHAR(10))
        PRINT ERROR_MESSAGE()
      END CATCH
    End

GO
-- tests
set nocount on
insert into toto values (1)  -- is not inserted on purpose by  the trigger
select * from toto   -- other value inserted despite the error
select * from toto2  -- other value inserted in other table despite the error

Answer 5

To avoid losing the transactional data prior to the trigger action, you'll want to call COMMIT TRAN. Do this before the TRY/CATCH block and you will get your desired results.

Example:

COMMIT TRAN
BEGIN TRY
    -- possible error occurs here...
END TRY
BEGIN CATCH
    PRINT 'Error on line ' + CAST(ERROR_LINE() AS VARCHAR(10))
    PRINT ERROR_MESSAGE()
END CATCH

It will throw the following error still - not sure how to avoid:

The transaction ended in the trigger. The batch has been aborted.

But both the original transaction and the trigger transaction should commit successfully.

UPDATE: To avoid the exception last error, call BEGIN TRAN within the TRY statement. Note, Microsoft recommends to NOT call COMMIT TRAN within a trigger, but if unavoidable, this should work for you.

Example:

COMMIT TRAN
BEGIN TRY
    BEGIN TRAN

Answer 6

Isn't the best way but it works. Start a new transaction and do the normal commit rollback and begin another transaction in the end for implicit transaction commit

http://msdn.microsoft.com/en-us/library/ms187844(v=SQL.90).aspx

Answer 7

u07ch,

Unfortunately you cannot use save transaction and try.. catch together - they simply cannot work together:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/15/avoid-mixing-old-and-new-styles-of-error-handling.aspx

Answer 8

It might be helpful to know what your are trying to do in the trigger.

The trigger is part of the transaction that sent the data to the inserted or deleted tables. If it fails, it will rollback the whole transaction. If you are expecting the trigger to fail occasionally but not rollback the statement that casued the trigger to fire, then perhaps you need to rethink whether a trigger is the right thing to use.

Answer 9

You can set XACT_Abort to OFF in the beginning of the trigger.