Trigger without a transaction?

Question

Is it possible to create a Trigger that will not be in a transaction?

I want to update data on a linked server with a trigger but due to firewall issues we can't create a distributed transaction between the two servers.

Answer 1

I know it's not helpful, so I'll probably get downvoted for this, but really, the solution is to fix the firewall problem.

I think if you use remote (not linked) servers (which are not the preferred option these days) then you can use SET REMOTEPROCTRANSACTIONS OFF to prevent the use of DTC for remote transactions, which might do the right thing here. But that probably doesn't help you with a linked server anyway.

Answer 2

What you probably want is a combination of a queue that contains updates for the linked server and a process that reads data from the queue and updates the remote server. The trigger will then insert a message into the queue as part of the normal transaction. This data will be read by the separate process and used to update the remote server. Logic will needed in the process handle errors (and possibly retries).

The queue can be implemented with one or more tables.