34,587
社区成员
发帖
与我相关
我的任务
分享
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
--在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