程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 在SQL Server中對視圖進行增刪改

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

編輯:關於SqlServer

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

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

image

為使用方便,建立一個視圖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是不可能的,執行時會發生以下錯誤:

image

在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;

執行結果如下:

image

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

測試結果:

image

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

運行結果:

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的操作,因此不需要修改任何存儲過程和源代碼,當然也不會對你原有的數據庫造成影響。

  1. 上一頁:
  2. 下一頁: