两个表A,B B.binfo<==>A.ID
Tab A
ID IDname
1 A
2 B
3 C
... ...Tab B
bid bname binfo
1 qq 3
2 ww 3
3 ee 2create view viewtest
as
select bname,func(binfo) as viewinfo from B ----func(binfo)是通过B.binfo<==>A.ID来获取A中的IDname之后查询此视图viewtest
bname viewinfo
qq C
ww C
ee B问题:现在我想直接操作视图,更改视图中viewinfo的值,同时B中的binfo的值也通过A跟着改动,若A没有关联值,则置为null。请问如何操作。
例:如将ww的viewinfo改为B,则B表中的bid=2的值3 变为 2. 如果将viewinfo改为K,则B表中的bid=2的值3 变为 null
Tab A
ID IDname
1 A
2 B
3 C
... ...Tab B
bid bname binfo
1 qq 3
2 ww 3
3 ee 2create view viewtest
as
select bname,func(binfo) as viewinfo from B ----func(binfo)是通过B.binfo<==>A.ID来获取A中的IDname之后查询此视图viewtest
bname viewinfo
qq C
ww C
ee B问题:现在我想直接操作视图,更改视图中viewinfo的值,同时B中的binfo的值也通过A跟着改动,若A没有关联值,则置为null。请问如何操作。
例:如将ww的viewinfo改为B,则B表中的bid=2的值3 变为 2. 如果将viewinfo改为K,则B表中的bid=2的值3 变为 null
if object_id('A') is not null
drop table [A];
go
create table [A] (id int, idname char(1));
go
insert into [A] select 1,'A' union all select 2,'B' union all select 3,'C';
goif object_id('B') is not null
drop table [B];
go
create table [B] (bid int, bname char(2), binfo int);
go
insert into [B] select 1,'qq',3 union all select 2,'ww',3 union all select 3,'ee',2;
goif object_id('v') is not null
drop view v;
go
create view [V]
as
select [B].bname,[A].idname viewinfo from [B] left join [A] on [A].id=[B].binfo
gocreate trigger v_update on [V]
instead of update
as
if update(viewinfo)
begin
update [B] set binfo=[A].id
from [B] inner join [inserted] i on [B].bname=i.bname
left join [A] on [A].idname=i.viewinfo;
end
goselect * from [V];
/*
qq C
ww C
ee B
*/update [V] set viewinfo='A' where bname='qq';
update [V] set viewinfo='Z' where bname='ww';select * from [V];
/*
qq A
ww NULL
ee B
*/
select * from [B];
/*
1 qq 1
2 ww NULL
3 ee 2
*/