sqlserver 跨服务器同步更新insert update表数据

–server124为远程服务器的别名

exec sp_addlinkedserver 'Testdb108', ' ', 'SQLOLEDB', '192.168.56.108' 
exec sp_addlinkedsrvlogin 'Testdb108', 'false ',null, 'sa', 'Bai_yun123'

EXEC sp_addlinkedserver
   @server = N'Students108', -- 链接服务器的别名
   @srvproduct = N' ',
   @provider = N'SQLNCLI', -- SQL Server Native Client Provider
   @datasrc = N'192.168.56.108'; -- 目标服务器的实际名称或IP地址
GO

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'Students108',
    @useself = 'FALSE',
    @locallogin = NULL, 
    @rmtuser = N'sa', -- 远程服务器的用户名
    @rmtpassword = N'Bai_yun123'; -- 远程服务器的密码
GO

--删除远程链接服务器信息
exec sp_dropserver 'Testdb', 'droplogins'


exec sp_serveroption @server='Testdb108', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='Testdb108', @optname='rpc out', @optvalue='true'
EXEC sp_serveroption @server = 'Testdb108',@optname = 'remote proc transaction promotion', @optvalue = 'true'

--创建插入的触发器-成功插入到源端服务器表 [OK]

CREATE TRIGGER [dbo].trg_InsertIntoRemoteTable
 on [dbo].students_local
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON
  SET XACT_ABORT ON 
 INSERT INTO Testdb108.Testdb.dbo.students_sync(stu_num,stu_name,stu_gender,stu_age)
  SELECT stu_num,stu_name,stu_gender,stu_age FROM inserted;
END
GO





--跨服务器更新触发器

CREATE TRIGGER trg_UpdateIntoRemoteTable
ON students_local
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON 

    UPDATE Testdb108.Testdb.dbo.students_sync
    SET stu_name = inserted.stu_name,
        stu_gender = inserted.stu_gender,
        stu_age = inserted.stu_age

    FROM inserted
    INNER JOIN deleted
        ON inserted.stu_num = deleted.stu_num
    INNER JOIN Testdb108.Testdb.dbo.students_sync a
        ON a.stu_num = inserted.stu_num
    WHERE 
        -- 下面的条件用于检查任何列是否发生了变化
        (a.stu_name <> inserted.stu_name OR 
         a.stu_gender <> inserted.stu_gender OR 
         a.stu_age <> inserted.stu_age)
END;
GO


--查询本地库表数据
select * from students_local

--查询从库表数据
select a.* from Testdb108.Testdb.dbo.students_sync a

--测试数据 
insert into students_local values(1006,'remotesync','mail',60);
delete from students_local where stu_num=1004;
update students_local set stu_age=18,stu_name='kooo' where stu_num=1003;