Post reply

can we insert the value into linked server tables using trigger ?

  • April 21, 2008 at 10:58 pm

    #181558

    i have two servers also i have same tables in both server.

    i want to replicate the values into another server when i inserted in my main server tables. for that i have written one insert trigger and tried to insert into linked server tables while trying to insert i am getting error.

    can any one tell me is it possible to insert the value into linked server tables using trigger ?

  • April 22, 2008 at 6:25 am

    #805451

    Yes, it is possible. I would not typically recommend it.

    A trigger is part of the original transaction. So, if your linked server is down or you have a packet lost your original transaction will fail and roll back. In addition, the original transaction needs to wait for the trigger to complete and inserting and updating a linked server is typically pretty slow.

    You may want to look into replication or service broker for what you are trying to do.

  • April 23, 2008 at 3:20 am

    #805941

    Thanks.

    I want to replicate the data to linked server.

  • April 23, 2008 at 6:51 am

    #806022

    Wouldn't a better way be to use the trigger to write to a transaction table, then to have a web service go through the transactions on a regular basis and process them?

    I've seen that done on a large Oracle based system

  • April 23, 2008 at 7:03 am

    #806032

    That is basically what Service Broker would do - without writing the code to manage the queues on both ends.

  • October 6, 2009 at 6:16 am

    #1062186

    hi , i too also have same issue here,, i can do update or select but i want to do insert record in linked server talbe of same values for local table.. how can i pass the values to linked server.. plase tell me any eg of code... thanks a lot..

  • October 6, 2009 at 8:41 am

    #1062297

    Please, please listen to Michael Earl. Look into Service Broker or just roll your own asynchronous solution. Also you could do SQL Server replication.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply