Is it possible to prevent a trigger from starting in a transaction?

Question

According to several resources like this ,

A request that is executed in the context of a trigger is automatically verified in a transaction. If there are any distributed requests in the trigger code, the transaction automatically propagates to the distributed transaction.

The simple question is, is there a way to prevent this behavior? I'm looking for a way to explicitly prevent code from running in my trigger in a transaction context.

Answer 1

If you are trying to do something asynchronous, so that the calling transaction does not have to wait, you can consider Service Broker , which is designed for this - burn some asynchronous task and return control to the caller, regardless of the scope of the transaction.

Another idea is that your trigger does not do the work, but instead processes the work item in the queue table and constantly runs a background process to process the queue. This is not always easy to do if your work item is working with a data set in inserted / deleted , but without additional context it certainly seems to be a viable option.

I do not know how to prevent the trigger from starting from the calling transaction. In fact, this is just the point.

Answer 2

This is called an "offline transaction", and the easiest way to implement it is to create a linked server to point to the source database.

See this MSDN blog for a possible solution.