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?