Here's my scenario:
First, I have two servers. In Server1 I have set up a Linked Server to Server2.
I have two databases on Server1. The first is the Customer database and the second is my Staging database. Server2 contains a database called Gateway.
I have AFTER INSERT, UPDATE triggers on tables in the Customer database that will insert records into tables in my Staging database.
The triggers on the tables in the Staging database will insert records into tables in the Gateway database on the Linked Server, Server2.
OK ... that is the setup. Now, the problem:
As you all probably know (and I didn't know until I started working with this), the statement in the Customer database that caused the trigger to fire, enlists everything in a transaction. If there is a problem several hops away with accessing the LinkedServer,
then the entire thing is rolled back. IOW, the records in the table on the Customer database are never updated! This is bad (we can't mess with the functionality of our Customer's database, so our triggers there have to be fairly foolproof).
The trigger in the Customer database which INSERTS records into the Staging database should be ok and never fail (they are, after all, on the same Server). However, the trigger in the Staging database is where there could potentially be a problem if the
LinkedServer is do
View Complete Post