--a.在srv1..author中创建触发器,实现数据即时同步
--新增同步
create trigger tr_insert_author On author
for insert
as
set xact_abort On
insert srv2.库名.dbo.author(ID,Name,telphone)
select ID,Name,telphone from inserted
go
--修改同步
create trigger tr_update_author On author
for update
as
set xact_abort On
update B set Name=i.Name,telphone=i.telphone
from srv2.库名.dbo.author B,inserted i
where B.ID=i.ID
go
--删除同步
create trigger tr_delete_author On author
for delete
as
set xact_abort On
delete B
from srv2.库名.dbo.author B,deleted d
where B.ID=d.ID
go
--3.实现同步处理的方法2,定时同步
--在srv1中创建如下的同步处理存储过程
create proc p_process
as
--更新修改过的数据
update B set Name=i.Name,telphone=i.telphone
from srv2.库名.dbo.author B,author i
where B.ID=i.ID And
(B.Name<>i.Name or B.telphone<>i.telphone)
--插入新增的数据
insert srv2.库名.dbo.author(ID,Name,telphone)
select ID,Name,telphone from author i
where not exists(
select * from srv2.库名.dbo.author where ID=i.ID)
--删除已经删除的数据(如果需要的话)
delete B
from srv2.库名.dbo.author B
where not exists(
select * from author where ID=B.ID)
go