delete 删除视图,是否可以达到同时删除多表的效果呢


摘要:
下文通过举例讲述delete对视图的删除效果,如下所示:
实验环境:sql server 2008 R2


   /* 
   1. 新建两张物理表
   2. 分别向表中插入相应的数据
   3. 新建视图

   4. 执行删除视图中的数据
   5. 查看物理表中的数据

   6. 删除视图、删除基础表
   */
create table [maomao365.com_A]
(keyIdA int,infoA nvarchar(30))

create table [maomao365.com_B]
(keyIdB int,infoB nvarchar(30))
go

insert into [maomao365.com_A]
(keyIdA,infoA)values
(1,'sqlblog'),(2,N'sql教程'),
(10,'s')
go
insert into [maomao365.com_B]
(keyIdB,infoB)values
(1,'sqlblog'),(2,N'sql教程')
go

create view vw_maomao
as  
       select 
       a.keyIdA,a.infoA,
       b.keyIdB,b.infoB 
       from [maomao365.com_A] a
        left join 
        [maomao365.com_B] b 
        on a.keyIdA = b.keyIdB 
go


/*
删除数据出错,出现影响多个基表,禁止删除
*/
delete from vw_maomao 
where keyIdA =10 
go

/*
修改视图为子查询模式
*/
alter view vw_maomao
as 
 select a.keyIdA,a.infoA
  from [maomao365.com_A] a
    where a.keyIdA in 
    ( 
     select keyIdb from [maomao365.com_B]
    )     
       go

select * from vw_maomao 
print '--重新执行删除操作--'
delete from vw_maomao 
where keyIdA =1 
go

go 
drop     view  vw_maomao

go
truncate table  [maomao365.com_A]
drop     table  [maomao365.com_A]

go
truncate table  [maomao365.com_B]
drop     table  [maomao365.com_B]


从以上的例子,我们可以看出如果视图涉及多个表时,我们不能对视图执行delete操作,只有当视图中涉及一个基础表时,才可以对视图执行删除操作,
即视图同时删除多表数据的情况是不可能发生。