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

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 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

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 4

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.