1

I have a trigger on a table that kicks off several other processes. However, I would like to ensure that even if the other processes fail, the insert is successfully committed to the original table.

I've been doing this by issuing an explicit COMMIT TRAN in the trigger before calling my other procedure. This seems to have been working fine, except for an unsightly error message:

Msg 3609, Level 16, State 1, Line 3 The transaction ended in the trigger. The batch has been aborted.

Upon doing a bit more research I changed the procedure, adding SET XACT_ABORT OFF; at the very top, and removed the explicit commit. This seems to have had the same effect.

Which one of these is preferable? Are there any pitfalls I should be aware of with either method?

2
  • Look into service broker to kick off these other processes in another transaction. Dec 12, 2014 at 19:08
  • @MartinSmith I'm already using service broker.
    – Mansfield
    Dec 12, 2014 at 19:11

1 Answer 1

1

If you have no other code in the trigger that should be allowed to cancel the INSERT, then try the following:

  • Keep the XACT_ABORT OFF
  • Put the COMMIT TRAN back
  • Add a BEGIN TRAN at the end of the trigger

This should be the same issue as if you have an open transaction, exec a proc, and in that proc do a ROLLBACK (or even just a BEGIN TRAN and no COMMIT or ROLLBACK): it will error saying value of @@TRANCOUNT is different between entering and exiting the proc.

7
  • Well, I'm not getting any error message. What is the point of XACT_ABORT in this situation if I'm already doing a commit?
    – Mansfield
    Dec 12, 2014 at 19:34
  • @Mansfield Because XACT_ABORT controls whether or not any error fails the batch. You have already said that you don't want that. Maybe it is ok to leave as ON, but you should test by putting a SELECT 1/0; in the trigger to see the effect. Dec 12, 2014 at 19:40
  • Okay, so, just so I understand: COMMIT TRAN ensures the data gets committed (obviously), BEGIN TRAN prevents an error from being thrown, and XACT_ABORT makes the batch succeed despite any errors?
    – Mansfield
    Dec 12, 2014 at 19:44
  • @Mansfield Well, doing that simple test will be better than my memory, but I still think you need, or should have, a TRY / CATCH in the trigger so you can handle errors appropriately. Dec 12, 2014 at 19:49
  • I've done the test, and it worked - I just want to make sure I understand what I'm doing. I already had the try/catch in there. Thanks for your help!
    – Mansfield
    Dec 12, 2014 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.