首页 > 代码库 > 在SQL Server中对视图进行增删改

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

原文:在SQL Server中对视图进行增删改

Lesktop开源IM发布以后,有一些网友问及如何在嵌入IM后与自己网站的用户系统整合(即如何让嵌入的IM直接使用网站原有的用户数据库,而不需要将已有的用户数据导入到IM的数据库中)。Lesktop对Users表(存储用户登录名,昵称,密码等信息的表)都是在存储过程中进行增删改的,显然,如果直接去改Users表相关的存储过程是比较麻烦的,本文将介绍一种较为简单的方法,在不需要修改存储过程和源代码的情况下整合用户系统

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

image

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

CREATE VIEW [dbo].[Users]asSELECT b.ID as ID, b.Name as Name, e.Remark as Remark FROM UserBase b, UserExtent e WHERE b.ID = e.ID;
<style type="text/css">.csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; }</style>

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

image

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

CREATE TRIGGER [dbo].[Users_Insert] ON [dbo].[Users] INSTEAD OF INSERTasdeclare @name nvarchar(32), @remark nvarchar(32)declare ins_cursor cursorforselect Name, Remark from insertedopen ins_cursorfetch 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;endclose 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 #tempdrop table #tempselect * from Users;select * from UserBase;select * from UserExtent;

执行结果如下:

image

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

CREATE TRIGGER [dbo].[Users_Update] ON [dbo].[Users] INSTEAD OF UPDATEasupdate UserExtentset UserExtent.Remark=ins.Remarkfrom inserted inswhere UserExtent.ID = ins.ID;update UserBaseset UserBase.Name=ins.Namefrom inserted inswhere 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;
<style type="text/css">.csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; }</style>

测试结果:

image

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

CREATE TRIGGER [dbo].[Users_Delete] ON [dbo].[Users] INSTEAD OF DELETEasdelete 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;
<style type="text/css">.csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; }</style>

运行结果:

image

上文已介绍了如何对视图进行增删改,接下来将介绍如何通过建立视图并添加增删改触发器实现Lesktop开源IM用户系统的整合。首先介绍一下Lesktop开源IM数据库中Users表的结构:

image

假使您的网站的用户表(假使名称为MyUserTable)只有Name,Nickname:

image

那么,您可以建立一张扩展表(假使名称为UserExtentIM),用于存储其他信息:

image

接下来,您只需要把Users表删掉,重新建立一个名称为Users的视图,然后用上文处理Users, UserBase, UserExtent的方法,在Users视图上建好触发器,在触发器中对MyUserTable,UserExtentIM表进行增删改即可,Lesktop的存储过程对User进行读取和增删改时,将通过触发器自动转换成对MyUserTable,UserExtentIM的操作,因此不需要修改任何存储过程和源代码,当然也不会对你原有的数据库造成影响。