对于多表的视图,你可以UPDATE记录,但无法删除表记录。所以你可以通过SP或直接对t1表删除。非单表视图 肯定只读!??? create table t1([no] int primary key identity(1,1),id int,[desc] nvarchar(20)) go create table t2([id] int primary key ,[name] nvarchar(100)) go insert into t1 values(1,'test1') insert into t1 values(2,'test2') insert into t1 values(3,'test3') insert into t1 values(4,'test4') go insert into t2 values(1,'name1') insert into t2 values(2,'name2') insert into t2 values(3,'name3') insert into t2 values(4,'name4') go create view v_getdetail as select a.[no],b.[name],a.[desc] from t1 a inner join t2 b on a.id=b.id go select * from v_getdetail--test update v_getdetail update v_getdetail set [desc]='update test4' where [no]=4--test delete v_getdetail delete v_getdetail where no=3--error View or function 'v_getdetail' is not updatable because the modification affects multiple base tables. drop table t1 drop table t2
create table t1([no] int primary key identity(1,1),id int,[desc] nvarchar(20))
go
create table t2([id] int primary key ,[name] nvarchar(100))
go
insert into t1 values(1,'test1')
insert into t1 values(2,'test2')
insert into t1 values(3,'test3')
insert into t1 values(4,'test4')
go
insert into t2 values(1,'name1')
insert into t2 values(2,'name2')
insert into t2 values(3,'name3')
insert into t2 values(4,'name4')
go
create view v_getdetail
as
select a.[no],b.[name],a.[desc] from t1 a
inner join t2 b on a.id=b.id
go
select * from v_getdetail--test update v_getdetail
update v_getdetail
set [desc]='update test4'
where [no]=4--test delete v_getdetail
delete v_getdetail
where no=3--error View or function 'v_getdetail' is not updatable because the modification affects multiple base tables.
drop table t1
drop table t2
1.通过DISTINCT建立的;
2.通过UNION建立的;
3.INSERT不包括父表的所有非空列;
4.UPDATE/delete 包括多表;
delete from t1 where t1.no in (select no from deleted)
go