Insert Data in Linked Server Table Using Insert Trigger and Instaed of Trigger

Hi All

We have situation where we need to insert data into Linked Server Table using For Insert Trigger on source table. On the same time we have Instead of Trigger on this Linked Server Table (destination) to update the records before insert into this table.

I have Implemented the solution but some how it is breaking now and again and stop inserting data. Top of this other Triggers on the source table also stop working. But after some time Triggers start working again...

I have tried to search the solution or clue of this behviour over the net but could not find any solution.

Pls help me here to solve this...

Thanks !!!

My understanding is in your trigger, you first insert into remote server and then insert into the current database.
When you execute linked server queries you start a distributed transaction. Any failure in the remote server can cause the trigger to raise an error. When an error is raised within a trigger, the trigger stop the execution. Then the next statement will not be executed. It could be the cause.
for a statement to fail in remote server, there could be many reasons. Are you getting any errors?

No... first trigger initiate on the table on same server e.g. ON INSERT and try to insert into the remote table but before insertion in Remote table a INSTEAD of trigger take place on this Remote Table to validate the data before insertion. This process hang the and lock the table on source server (first table). Source Server is working with Real Time data that inserting every secs.

As you said " Any failure in the remote server can cause the trigger to raise an error. When an error is raised within a trigger, the trigger stop the execution. Then the next statement will not be executed. It could be the cause.
for a statement to fail in remote server, there could be many reasons. "

If this is case then pls advice me to alternative method(s)...

Thanks
sqlderby

if you are doing the insert by calling the proc on the host server which then inserts into the remote you can try/catch on the proc so if it fails you can insert the failing row in the host server in an error table and then insert those in less busy hours to the remote... or am I missing something? could be with me

If you are using the procedure, my advice is to move the trigger logic to procedure.