if exists(select 1 from sysobjects where id = object_id(N't1') and type = 'U') drop table t1 gocreate table t1( f0 int identity(1,1), f1 nvarchar(10), f2 nvarchar(10) ) goinsert into t1(f1,f2) values('1','1') insert into t1(f1,f2) values('2','2') insert into t1(f1,f2) values('3','3') insert into t1(f1,f2) values('4','4') goif exists(select 1 from sysobjects where id = object_id(N't2') and type = 'U') drop table t2 gocreate table t2( f0 int identity(1,1), f1 nvarchar(10), f2 nvarchar(10) ) goinsert into t2(f1,f2) values('1','1') insert into t2(f1,f2) values('2','2') insert into t2(f1,f2) values('3','3') insert into t2(f1,f2) values('4','4') goif exists(select 1 from sysobjects where id = object_id(N'Myview') and type = 'V') drop view Myview gocreate view Myview as select t1.f0,t1.f1,t1.f2,t2.f1 f3, t2.f2 f4 from t1 left join t2 on t1.f0 = t2.f0 godelete from myview where f0 = 4------------------------ 服务器: 消息 4405,级别 16,状态 1,行 1 视图或函数 'myview' 不可更新,因为修改会影响多个基表。
if exists(select 1 from sysobjects where id = object_id(N't1') and type = 'U') drop table t1 gocreate table t1( f0 int identity(1,1), f1 nvarchar(10), f2 nvarchar(10) ) goinsert into t1(f1,f2) values('1','1') insert into t1(f1,f2) values('2','2') insert into t1(f1,f2) values('3','3') insert into t1(f1,f2) values('4','4') goif exists(select 1 from sysobjects where id = object_id(N't2') and type = 'U') drop table t2 gocreate table t2( f0 int identity(1,1), f1 nvarchar(10), f2 nvarchar(10) ) goinsert into t2(f1,f2) values('1','1') insert into t2(f1,f2) values('2','2') insert into t2(f1,f2) values('3','3') insert into t2(f1,f2) values('4','4') goif exists(select 1 from sysobjects where id = object_id(N'Myview') and type = 'V') drop view Myview gocreate view Myview as select t1.f0,t1.f1,t1.f2,t2.f1 f3, t2.f2 f4 from t1 left join t2 on t1.f0 = t2.f0 goif exists(select 1 from sysobjects where id = object_id(N'MyTrigger')) drop view MyTrigger go create trigger MyTrigger on Myview instead of delete as delete from t1 where t1.f0 in (select f0 from deleted) godelete from myview where f0 = 4 --------------- 执行成功
VIEW可以理解成临时表。
drop table t1
gocreate table t1(
f0 int identity(1,1),
f1 nvarchar(10),
f2 nvarchar(10)
)
goinsert into t1(f1,f2) values('1','1')
insert into t1(f1,f2) values('2','2')
insert into t1(f1,f2) values('3','3')
insert into t1(f1,f2) values('4','4')
goif exists(select 1 from sysobjects where id = object_id(N't2') and type = 'U')
drop table t2
gocreate table t2(
f0 int identity(1,1),
f1 nvarchar(10),
f2 nvarchar(10)
)
goinsert into t2(f1,f2) values('1','1')
insert into t2(f1,f2) values('2','2')
insert into t2(f1,f2) values('3','3')
insert into t2(f1,f2) values('4','4')
goif exists(select 1 from sysobjects where id = object_id(N'Myview') and type = 'V')
drop view Myview
gocreate view Myview
as
select t1.f0,t1.f1,t1.f2,t2.f1 f3, t2.f2 f4
from t1 left join t2 on t1.f0 = t2.f0
godelete from myview where f0 = 4------------------------
服务器: 消息 4405,级别 16,状态 1,行 1
视图或函数 'myview' 不可更新,因为修改会影响多个基表。
drop table t1
gocreate table t1(
f0 int identity(1,1),
f1 nvarchar(10),
f2 nvarchar(10)
)
goinsert into t1(f1,f2) values('1','1')
insert into t1(f1,f2) values('2','2')
insert into t1(f1,f2) values('3','3')
insert into t1(f1,f2) values('4','4')
goif exists(select 1 from sysobjects where id = object_id(N't2') and type = 'U')
drop table t2
gocreate table t2(
f0 int identity(1,1),
f1 nvarchar(10),
f2 nvarchar(10)
)
goinsert into t2(f1,f2) values('1','1')
insert into t2(f1,f2) values('2','2')
insert into t2(f1,f2) values('3','3')
insert into t2(f1,f2) values('4','4')
goif exists(select 1 from sysobjects where id = object_id(N'Myview') and type = 'V')
drop view Myview
gocreate view Myview
as
select t1.f0,t1.f1,t1.f2,t2.f1 f3, t2.f2 f4
from t1 left join t2 on t1.f0 = t2.f0
goif exists(select 1 from sysobjects where id = object_id(N'MyTrigger'))
drop view MyTrigger
go
create trigger MyTrigger on Myview
instead of delete
as
delete from t1 where t1.f0 in (select f0 from deleted)
godelete from myview where f0 = 4
---------------
执行成功