Datatrigger after insertion - insert into the table ON ANOTHER SERVER

Question

I have two separate SQL servers, call them A_server and B_server. When there is insert / delete / update in A_server, the row must be inserted / deleted / updated on the B_server server. I found a ton of examples of how to encode a data trigger when both tables are on the SAME server, for example:

CREATE TRIGGER yourNewTrigger ON yourSourcetable
FOR INSERT
AS
   INSERT INTO yourDestinationTable
   (col1, col2    , col3, user_id, user_name)
   SELECT 'a'
        , default
        , null
        , user_id
        , user_name
   FROM inserted
GO 

But I did not find a single example of a data trigger where it ran between tables on DIFFERENT servers. Where does the connection string go exactly and what does it look like?

Answer 1

Perhaps this may help you.

First create a linked server

use master
exec sp_addlinkedserver
   @server='NameOf_LinkedServer', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='yourServer\instance1';

and then point to it

CREATE TRIGGER yourNewTrigger ON yourSourcetable
FOR INSERT
AS
   INSERT INTO NameOf_LinkedServer.DataBaseName.Owner.yourDestinationTable
   (col1, col2    , col3, user_id, user_name)
   SELECT 'a'
        , default
        , null
        , user_id
        , user_name
   FROM inserted