通过视图更新数据

-示例

--测试数据
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 行）

--*/