在SQL Server中对视图进行增删改(转)

看到好多技术论坛上误导人说:视图只能进行查询，不能进行增删改,所以转发一篇大牛的文章：

介绍一下在SQL SERVER中，如何对视图进行增删改。假使用户有Name,Remark两项信息，但是没有存放在同一张表中，而是分开存储在两个表UserBase(ID, Name)，UserExtent(ID, Remark)中。

为使用方便，建立一个视图Users，用于表示用户的完整信息，其定义如下：

CREATE VIEW [ dbo ] . [ Users ]
as
SELECT b.ID as ID, b.Name as Name, e.Remark as Remark
FROM UserBase b, UserExtent e
WHERE b.ID = e.ID

现在，我们希望通过Users视图进行增删改实现对UserBase，UserExtent表进行修改。显然，如果对Users直接执行insert，update，delete是不可能的，执行时会发生以下错误：

在SQL Server中，对视图增删改可以通过触发器来实现，例如我们可以创建一个INSERT触发器，当在视图Users上执行INSERT时，在触发器中实现对UserBase，UserExtent的INSERT操作。在触发器中，可以通过名称为inserted的表，获取到新插入的行，具体代码如下：

CREATE TRIGGER [ dbo ] . [ Users_Insert ] ON [ dbo ] . [ Users ] INSTEAD OF INSERT
as
declare @name nvarchar ( 32 ), @remark nvarchar ( 32 )
declare ins_cursor cursor
for
select Name, Remark from inserted
open ins_cursor
fetch next from ins_cursor into @name , @remark ;
while ( @@fetch_status = 0 )
begin
-- 读取所有行，并插入
insert into UserBase (Name) values ( @name );
insert into UserExtent(ID, Remark) values ( @@identity , @remark );
fetch next from ins_cursor into @name , @remark ;
end
close ins_cursor

下面我们通过插入两行数据测试触发器：

-- 清空所有数据
delete from UserExtent;
delete from UserBase;

create table # temp (
name nvarchar ( 32 ),
remark nvarchar ( 32 )
)
insert # temp (name,remark) values (N ' user1 ' , N ' 1 ' );
insert # temp (name,remark) values (N ' user2 ' , N ' 2 ' );

-- 插入两行数据
insert Users(name, remark)
select name,remark from # temp

drop table # temp

select * from Users;
select * from UserBase;
select * from UserExtent;

执行结果如下：

创建更新触发器，与INSERT触发器类似，受影响的行会保存在inserted中，可以从inserted表中获取受影响的行，并更新UserBase，UserExtent，具体代码如下：

CREATE TRIGGER [ dbo ] . [ Users_Update ] ON [ dbo ] . [ Users ] INSTEAD OF UPDATE
as
update UserExtent
set UserExtent.Remark = ins.Remark
from inserted ins
where UserExtent.ID = ins.ID;

update UserBase
set UserBase.Name = ins.Name
from inserted ins
where UserBase.ID = ins.ID;

测试代码：

-- 清空所有数据
delete from UserExtent;
delete from UserBase;
-- 插入两行数据
insert Users (name,remark) values (N ' user1 ' , N ' 1 ' );
insert Users (name,remark) values (N ' user2 ' , N ' 2 ' );
insert Users (name,remark) values (N ' user3 ' , N ' 2 ' );
-- 修改后两行数据
UPDATE Users set Remark = N ' 3 ' where Remark = N ' 2 '
-- 输出数据
select * from Users;
select * from UserBase;
select * from UserExtent;

测试结果：

创建删除触发器，在删除的触发器中，可以通过deleted表，获取被删除的行，具体代码如下：

CREATE TRIGGER [ dbo ] . [ Users_Delete ] ON [ dbo ] . [ Users ] INSTEAD OF DELETE
as
delete from UserExtent where ID in ( select ID from deleted)
delete from UserBase where ID in ( select ID from deleted)

测试代码：

-- 清空所有数据
delete from UserExtent;
delete from UserBase;
-- 插入两行数据
insert Users (name,remark) values (N ' user1 ' , N ' 1 ' );
insert Users (name,remark) values (N ' user2 ' , N ' 2 ' );
insert Users (name,remark) values (N ' user3 ' , N ' 2 ' );
-- 删除后两行数据
delete from Users where Remark = N ' 2 '
-- 输出数据
select * from Users;
select * from UserBase;
select * from UserExtent;

运行结果：

原文：http://www.cnblogs.com/lucc/archive/2011/03/20/1989218.html

转载于:https://www.cnblogs.com/pfs1314/archive/2011/05/16/2047819.html