日期:2014-05-18 浏览次数:20637 次
--在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