楼主是需要这个吧。--一般情况下不允许对两个基表以上的view进行更新操作。 --但是可以用instead of trigger同时完成对多个表的insert/delete--instead of insert触发器在view上的使用--先创建两个基表用 create table test1 (a int,aa varchar(32)) create table test2 (b int,bb varchar(32)) go --创建基表的视图 create view v1 as select test1.*,test2.* from test1 ,test2 where test1.a=test2.b go--insert v1 values(1,'aa',1,'bb')--这个时候对该view进行insert将失败,因为不能对两个基表以上的视图进行更新--所以现在再对v1创建一个instead of insert trigger --create trigger/proc的句子比较单独运行create trigger trg_v1 on v1 --在视图上建立instead of insert触发器,在insert view前触发 instead of insert as begin insert test1 select a,aa from inserted insert test2 select b,bb from inserted end goinsert v1 values(1,'aa',1,'bb') --这个时候对view就可以进行insert了~Select * from test1 Select * from test2--当然,也可以创建instead of delete进行删除: create trigger trg_v2 on v1 instead of delete as begin delete from test1 where a in(select a from deleted) delete from test2 where b in(select a from deleted) end godelete from v1 where a=1 --对视图进行deleteSelect * from test1 Select * from test2--删除测试的所有对象 drop trigger trg_v1 drop trigger trg_v2 drop table test1 drop table test2 drop view v1 GO
--但是可以用instead of trigger同时完成对多个表的insert/delete--instead of insert触发器在view上的使用--先创建两个基表用
create table test1 (a int,aa varchar(32))
create table test2 (b int,bb varchar(32))
go
--创建基表的视图
create view v1
as
select test1.*,test2.* from test1 ,test2 where test1.a=test2.b
go--insert v1 values(1,'aa',1,'bb')--这个时候对该view进行insert将失败,因为不能对两个基表以上的视图进行更新--所以现在再对v1创建一个instead of insert trigger
--create trigger/proc的句子比较单独运行create trigger trg_v1 on v1 --在视图上建立instead of insert触发器,在insert view前触发
instead of insert
as
begin
insert test1 select a,aa from inserted
insert test2 select b,bb from inserted
end
goinsert v1 values(1,'aa',1,'bb') --这个时候对view就可以进行insert了~Select * from test1
Select * from test2--当然,也可以创建instead of delete进行删除:
create trigger trg_v2 on v1
instead of delete
as
begin
delete from test1 where a in(select a from deleted)
delete from test2 where b in(select a from deleted)
end
godelete from v1 where a=1 --对视图进行deleteSelect * from test1
Select * from test2--删除测试的所有对象
drop trigger trg_v1
drop trigger trg_v2
drop table test1
drop table test2
drop view v1
GO