在SQL Server中對視圖進行增刪改

Lesktop開源IM發布以後，有一些網友問及如何在嵌入IM後與自己網站的使用者系統整合(即如何讓嵌入的IM直接使用網站原有的使用者資料庫，而不需要將已有的使用者資料匯入到IM的資料庫中)。Lesktop對Users表（儲存使用者登入名稱，暱稱，密碼等資訊的表）都是在預存程序中進行增刪改的，顯然，如果直接去改Users表相關的預存程序是比較麻煩的，本文將介紹一種較為簡單的方法，在不需要修改預存程序和原始碼的情況下整合使用者系統。

為實現這個目的，先介紹一下在SQL SERVER中，如何對視圖進行增刪改。假使使用者有Name,Remark兩項資訊，但是沒有存放在同一張表中，而是分開儲存在兩個表UserBase(ID, Name)，UserExtent(ID, Remark)中。

為使用方便，建立一個視圖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;

現在，我們希望通過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 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;

執行結果如下：

建立更新觸發器，與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;

測試結果：

建立刪除觸發器，在刪除的觸發器中，可以通過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;

運行結果：

上文已介紹了如何對視圖進行增刪改，接下來將介紹如何通過建立視圖並添加增刪改觸發器實現Lesktop開源IM使用者系統的整合。首先介紹一下Lesktop開源IM資料庫中Users表的結構：

假使您的網站的使用者表(假使名稱為MyUserTable)只有Name,Nickname：

那麼，您可以建立一張擴充表(假使名稱為UserExtentIM)，用於儲存其他資訊：

接下來，您只需要把Users表刪掉，重建立立一個名稱為Users的視圖，然後用上文處理Users, UserBase, UserExtent的方法，在Users視圖上建好觸發器，在觸發器中對MyUserTable，UserExtentIM表進行增刪改即可，Lesktop的預存程序對User進行讀取和增刪改時，將通過觸發器自動轉換成對MyUserTable，UserExtentIM的操作，因此不需要修改任何預存程序和原始碼，當然也不會對你原有的資料庫造成影響。