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

作者: 盧春城來源: 博客園發布時間: 2011-03-23 10:42 閱讀: 7992 次推薦: 1 原文鏈接 [收藏]

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

　　為實現這個目的，先介紹一下在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;

　　運行結果：

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

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

　　那么，您可以建立一張擴展表(假使名稱為UserExtentIM)，用于存儲其他信息：

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

標簽：SQL Server