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

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 2

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