–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;