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

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

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

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

CREATEVIEW[dbo].[Users]asSELECTb.IDasID, b.NameasName, e.RemarkasRemarkFROMUserBase b, UserExtent eWHEREb.ID=e.ID;

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

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

CREATETRIGGER[dbo].[Users_Insert]ON[dbo].[Users]INSTEADOFINSERTasdeclare@namenvarchar(32),@remarknvarchar(32)declareins_cursorcursorforselectName, Remarkfrominsertedopenins_cursorfetchnextfromins_cursorinto@name,@remark;while(@@fetch_status=0)begin--读取所有行，并插入insertintoUserBase (Name)values(@name);insertintoUserExtent(ID, Remark)values(@@identity,@remark);fetchnextfromins_cursorinto@name,@remark;endcloseins_cursor

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

--清空所有数据deletefromUserExtent;deletefromUserBase;createtable#temp(

namenvarchar(32),

remarknvarchar(32)

)insert#temp(name,remark)values(N'user1', N'1');insert#temp(name,remark)values(N'user2', N'2');--插入两行数据insertUsers(name, remark)selectname,remarkfrom#tempdroptable#tempselect*fromUsers;select*fromUserBase;select*fromUserExtent;

执行结果如下：

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

CREATETRIGGER[dbo].[Users_Update]ON[dbo].[Users]INSTEADOFUPDATEasupdateUserExtentsetUserExtent.Remark=ins.Remarkfrominserted inswhereUserExtent.ID=ins.ID;updateUserBasesetUserBase.Name=ins.Namefrominserted inswhereUserBase.ID=ins.ID;

测试代码：

--清空所有数据deletefromUserExtent;deletefromUserBase;--插入两行数据insertUsers (name,remark)values(N'user1', N'1');insertUsers (name,remark)values(N'user2', N'2');insertUsers (name,remark)values(N'user3', N'2');--修改后两行数据UPDATEUserssetRemark=N'3'whereRemark=N'2'--输出数据select*fromUsers;select*fromUserBase;select*fromUserExtent;

测试结果：

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

CREATETRIGGER[dbo].[Users_Delete]ON[dbo].[Users]INSTEADOFDELETEasdeletefromUserExtentwhereIDin(selectIDfromdeleted)deletefromUserBasewhereIDin(selectIDfromdeleted)

测试代码：

--清空所有数据deletefromUserExtent;deletefromUserBase;--插入两行数据insertUsers (name,remark)values(N'user1', N'1');insertUsers (name,remark)values(N'user2', N'2');insertUsers (name,remark)values(N'user3', N'2');--删除后两行数据deletefromUserswhereRemark=N'2'--输出数据select*fromUsers;select*fromUserBase;select*fromUserExtent;

运行结果：

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

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

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

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