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