跨库触发器应用

--在数据库test中创建表aa
use test
go
create table aa(id int primary key not null,name1 varchar(20),name2 varchar(20),name3 varchar(20))
--在数据库test1中创建表bb
use test1
go
create table bb(id int primary key not null,name1 varchar(20),name2 varchar(20),name3 varchar(20))
--在数据库test2中创建表cc
use test2
go
create table cc(id int primary key not null,name1 varchar(20),name2 varchar(20),name3 varchar(20))
go
use test
go
--创建跨库触发器，实现aa表中每个id对应的name2,name3改变时，就相应改变数据库test1中的bb表和数据库test2中的cc表
CREATE TRIGGER test_aa on aa
for update,insert,delete
as
if not exists (select * from deleted) --插入
begin
insert test1..bb(id,name2) select id,name2 from inserted
insert test2..cc(id,name3) select id,name3 from inserted
end
else if not exists (select * from inserted)--删除
begin
delete test1..bb where id in (select id from deleted)
delete test2..cc where id in (select id from deleted)
end
else--更新
begin
update test1..bb set name2=inserted.name2 from inserted where test1..bb.id=inserted.id
update test2..cc set name3=inserted.name3 from inserted where test2..cc.id=inserted.id
end
go
--测试
select * from test..aa
select * from test1..bb
select * from test2..cc
go
use test
go
--插入数据
insert aa values(1,'aa','bb','cc')
insert aa values(2,'aa1','bb1','cc1')
insert aa values(3,'aa2','bb2','cc2')
--测试
select * from test..aa
select * from test1..bb
select * from test2..cc
go
use test
go
--删除数据
delete aa where id=2
go
--测试
select * from test..aa
select * from test1..bb
select * from test2..cc
go
use test
go
--更新数据
update aa set name2='bb22' where id=3
go
--测试
select * from test..aa
select * from test1..bb
select * from test2..cc
go