跨庫觸發器應用

--在資料庫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