XACT_ABORT OFF vs COMMIT in trigger 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?
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.