资料同步

--在A库和B库上都建一个如下的tb表
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 id int,
 name varchar(10)
)
go
--在A库的tb上建插入触发器
if object_id('tr_tb_forinsert') is not null
   drop trigger tr_tb_forinsert
go
create trigger tr_tb_forinsert on tb
for insert
as
  insert into B.dbo.tb select * from inserted
go
--在A库的tb上建更新触发器
if object_id('tr_tb_forupdate') is not null
   drop trigger tr_tb_forupdate
go
create trigger tr_tb_forupdate on tb
for update
as 
  update B.dbo.tb set id=t2.id,name=t2.name from B.dbo.tb t1 inner join inserted t2 on t1.id=t2.id
go
--在A库执行插入和更新语句，触发器会自动执行B库tb表的插入和更新
insert into tb select 1,'张三'
update tb set name='李四' where id=1

create trigger [dbo].[TableA_Trigger] on  [dbo].[TableA] for insert,update,delete 
as 
begin 
declare @operatetype char(1); 
Begin 
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) set @operatetype ='1'; 
else IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) set @operatetype ='2'; 
else  set @operatetype ='3'; 
end  
if(@operatetype ='2') 
begin 
INSERT INTO [TableA_LOG] ([a],[b],[Operatetype],[OperateTime]) 
select [a],[b],@operatetype,getdate() 
from deleted 
end 
else 
begin 
INSERT INTO [TableA_LOG] ([a],[b],[Operatetype],[OperateTime])  
select [a],[b],@operatetype,getdate()
from inserted 
end 
end
GO