SQL SERVER 2005中對(duì)視圖進(jìn)行增刪改操作技巧

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

為實(shí)現(xiàn)這個(gè)目的，先介紹一下在SQL SERVER中，如何對(duì)視圖進(jìn)行增刪改。假使用戶有Name,Remark兩項(xiàng)信息，但是沒有存放在同一張表中，而是分開存儲(chǔ)在兩個(gè)表UserBase(ID, Name)，UserExtent(ID, Remark)中。

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

.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; }

現(xiàn)在，我們希望通過Users視圖進(jìn)行增刪改實(shí)現(xiàn)對(duì)UserBase，UserExtent表進(jìn)行修改。顯然，如果對(duì)Users直接執(zhí)行insert，update，delete是不可能的，執(zhí)行時(shí)會(huì)發(fā)生以下錯(cuò)誤：

在SQL Server中，對(duì)視圖增刪改可以通過觸發(fā)器來(lái)實(shí)現(xiàn)，例如我們可以創(chuàng)建一個(gè)INSERT觸發(fā)器，當(dāng)在視圖Users上執(zhí)行INSERT時(shí)，在觸發(fā)器中實(shí)現(xiàn)對(duì)UserBase，UserExtent的INSERT操作。在觸發(fā)器中，可以通過名稱為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

下面我們通過插入兩行數(shù)據(jù)測(cè)試觸發(fā)器：

--清空所有數(shù)據(jù)
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');
--插入兩行數(shù)據(jù)
insert Users(name, remark)
select name,remark from #temp
drop table #temp
select * from Users;
select * from UserBase;
select * from UserExtent;

執(zhí)行結(jié)果如下：

創(chuàng)建更新觸發(fā)器，與INSERT觸發(fā)器類似，受影響的行會(huì)保存在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;

測(cè)試代碼：

--清空所有數(shù)據(jù)
delete from UserExtent;
delete from UserBase;
--插入兩行數(shù)據(jù)
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');
--修改后兩行數(shù)據(jù)
UPDATE Users set Remark = N'3' where Remark = N'2'
--輸出數(shù)據(jù)
select * from Users;
select * from UserBase;
select * from UserExtent;

.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; }

測(cè)試結(jié)果：

創(chuàng)建刪除觸發(fā)器，在刪除的觸發(fā)器中，可以通過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)

測(cè)試代碼：

--清空所有數(shù)據(jù)
delete from UserExtent;
delete from UserBase;
--插入兩行數(shù)據(jù)
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');
--刪除后兩行數(shù)據(jù)
delete from Users where Remark = N'2'
--輸出數(shù)據(jù)
select * from Users;
select * from UserBase;
select * from UserExtent;

.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; }

運(yùn)行結(jié)果：

上文已介紹了如何對(duì)視圖進(jìn)行增刪改，接下來(lái)將介紹如何通過建立視圖并添加增刪改觸發(fā)器實(shí)現(xiàn)Lesktop開源IM用戶系統(tǒng)的整合。首先介紹一下Lesktop開源IM數(shù)據(jù)庫(kù)中Users表的結(jié)構(gòu)：

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

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

接下來(lái)，您只需要把Users表刪掉，重新建立一個(gè)名稱為Users的視圖，然后用上文處理Users,UserBase,UserExtent的方法，在Users視圖上建好觸發(fā)器，在觸發(fā)器中對(duì)MyUserTable，UserExtentIM表進(jìn)行增刪改即可，Lesktop的存儲(chǔ)過程對(duì)User進(jìn)行讀取和增刪改時(shí)，將通過觸發(fā)器自動(dòng)轉(zhuǎn)換成對(duì)MyUserTable，UserExtentIM的操作，因此不需要修改任何存儲(chǔ)過程和源代碼，當(dāng)然也不會(huì)對(duì)你原有的數(shù)據(jù)庫(kù)造成影響。