Triggers on Linked Servers
-
We have a Web Site database (A) that collects orders and want a real time copy of that order on a local server (B) so that it can be processed. This is currently done by triggers inserting the records committed on (A) to the local (B) linked server. However it has been thought that some orders are not getting through, the network connection is via a VPN link which has been known to be down on occassions.
What could be happening when the link is down? Will the Web Site record still be committed if the triggers cannot fire?
This is the first time I've come accross a trigger via a linked server and am trying to understand how it works. The person who wrote this has since left the company and left no documentation (usual story).
Thanks Carolyn
-
The trigger is in the same transaction context of the DML that inserts the order into your table. The transaction will be promoted by MSDTC to a distributed transaction since the trigger asscess a linked server. So if the SP or Script that inserts order has proper transaction control, there should be no orders lost theorectically. If an order is inserted into A, it should be processed in B successfully, otherwise the insertion to A will be rolled back as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply