Is it possible to have a Distributed Transaction running within another distributed transaction and so on.
I have 3 servers(Servers A, B, and C). Server A has a linked server to Server B and Server B has a linked server to Server C. I have two triggers. One that resides on Server A and Updates Server B and Another on Server B that updates Server C. Trigger on Server A invokes a distributed transaction with Server B to Update it which in turn fires the trigger on Server B which invokes another distributed transaction with Server C.
Now is this possible?? If not how can I get Around this?? I can not just a distributed transaction on the trigger which resides on server A as the one on Server B can fire on its own and not always because of Server A.
Is it possible to have nested distributed transactions like Begin/Ends??
Every trigger runs within a transaction and this should be automatically upgraded to a distributed transaction for cross server operations so you shouldn't have to do any coding aprt from the set xact_abort on.