--示例
--测试数据
create table 表1(ID int identity(1,1),A varchar(10),B varchar(10),C varchar(10),D varchar(10)
,CONSTRAINT idx_表1_a UNIQUE(a))
insert 表1 select 'a', 'b','c','d'
union all select 'a2','b','c','d2'
union all select 'a3','c','b','a'
create table 表2(ID int identity(1,1),E varchar(10),A varchar(10),F varchar(10),G varchar(10)
,CONSTRAINT idx_表2_a UNIQUE(a))
insert 表2 select 'm','a' ,'w','q'
union all select 'x','a2','p','k'
union all select 'u','a3','i','r'
go
--视图
create view 视图名
as
select a.*,b.g
from 表1 a join 表2 b on a.a=b.a
go
--处理的触发器
create trigger tr_process on 视图名
instead of insert,update,delete
as
if exists(select 1 from inserted)
if exists(select 1 from deleted)
begin
select id=identity(int,1,1),a,b,c,d,g into #i from inserted
select id=identity(int,1,1),a into #d from deleted
update 表1 set a=i_a,b=i_b,c=i_c,d=i_d
from 表1 a join(
select i_a=i.a,i_b=i.b,i_c=i.c,i_d=i.d,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a
update 表2 set a=i_a,g=i_g
from 表2 a join(
select i_a=i.a,i_g=i.g,d_a=d.a
from #i i join #d d on i.id=d.id
)b on a.a=d_a
end
else
begin
insert 表1(a,b,c,d) select a,b,c,d from inserted
insert 表2(a,g) select a,g from inserted
end
else
begin
delete 表1 from 表1 a join deleted d on a.a=d.a
delete 表2 from 表2 a join deleted d on a.a=d.a
end
go
--显示视图
select * from 视图名
go
--测试触发器
update 视图名 set b=b+'bb',g=g+'gg'
insert 视图名 select 1,'aa','b','c','d','g'
delete 视图名 where a='a2'
go
--显示处理结果
select * from 视图名
go
--删除测试
drop view 视图名
drop table 表1,表2
/*--测试结果
--视图的效果
ID A B C D g
----------- ---------- ---------- ---------- ---------- ----------
1 a b c d q
2 a2 b c d2 k
3 a3 c b a r
（所影响的行数为 3 行）
--触发器处理的效果
ID A B C D g
----------- ---------- ---------- ---------- ---------- ----------
1 a bbb c d qgg
3 a3 cbb b a rgg
4 aa b c d g
（所影响的行数为 3 行）
--*/
