SQL Server 視圖增刪改實戰：利用 INSTEAD OF 觸發器整合異構用戶系統

前言：為什麼需要操作視圖？

在 Lesktop 開源即時通訊系統 發布後，許多開發者詢問如何將現有網站的用戶系統與嵌入式 IM 整合。核心需求是：讓 IM 直接使用網站原有的用戶資料庫，而無需將既有用戶數據遷移至 IM 的資料庫中。

Lesktop 對 Users 表（存放登入帳號、暱稱、密碼等）的增刪改操作均在預存程序中完成。若直接修改相關預存程序，不僅工作量大，還增加了後續維護的風險。本文將介紹一種無需修改預存程序與原始碼的整合方案——建立視圖並配合 INSTEAD OF 觸發器。

核心場景：跨表整合用戶資料

假設用戶的基本資訊（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 視圖執行 INSERT、UPDATE、DELETE，SQL Server 會拋出錯誤：

解決之道：使用 INSTEAD OF 觸發器攔截對視圖的 DML 操作，在觸發器內部手動拆解並寫入底層實體表。

實作步驟：三類觸發器的完整範例

1. 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
    -- 先寫入主表，取得自動產生的 ID
    INSERT INTO UserBase (Name) VALUES (@name);
    -- 再寫入擴充表，使用 @@IDENTITY 關聯
    INSERT INTO UserExtent (ID, Remark) VALUES (@@IDENTITY, @remark);

    FETCH NEXT FROM ins_cursor INTO @name, @remark;
END

CLOSE ins_cursor;
DEALLOCATE ins_cursor;

測試腳本：

-- 清空測試資料
DELETE FROM UserExtent; DELETE FROM UserBase;

-- 批次插入兩筆
CREATE TABLE #temp (name NVARCHAR(32), remark NVARCHAR(32));
INSERT #temp VALUES (N'user1', N'1'), (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;

2. UPDATE 觸發器：同樣從 inserted 取得異動後的值

CREATE TRIGGER [dbo].[Users_Update] ON [dbo].[Users]
INSTEAD OF UPDATE
AS
UPDATE UserExtent
SET Remark = ins.Remark
FROM inserted ins
WHERE UserExtent.ID = ins.ID;

UPDATE UserBase
SET 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');

-- 將 Remark='2' 的兩筆改為 '3'
UPDATE Users SET Remark = N'3' WHERE Remark = N'2';

SELECT * FROM Users;
SELECT * FROM UserBase;
SELECT * FROM UserExtent;

3. DELETE 觸發器：透過 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 整合案例。

1. 既有資料庫結構分析

Lesktop 原始 Users 表結構：

假設您的網站用戶表 MyUserTable 僅含 Name、Nickname：

2. 建立擴充表補齊欄位

新增 UserExtentIM 存放 IM 所需的額外欄位（密碼雜湊、狀態等）：

3. 替換原表為視圖並掛載觸發器

1. 刪除原有 Users 表（備份資料後）。
2. 建立同名視圖 Users，JOIN MyUserTable 與 UserExtentIM。
3. 依照上述範例，在視圖上建立 INSERT、UPDATE、DELETE 三個 INSTEAD OF 觸發器，內部操作 MyUserTable 與 UserExtentIM。

完成後，Lesktop 所有預存程序對 Users 的讀寫將自動透過觸發器轉導至您的業務表，**零修改原始碼、零侵入既有資料庫結構**。

總結

• 視圖 + INSTEAD OF 觸發器 是解決「多表合一、舊系統整合」的經典模式。
• 關鍵在於善用 inserted、deleted 虛擬表取得 DML 影響的資料列。
• 游標寫法雖囉嗦，但相容性最好；若為 SQL Server 2008+ 可改用 SET 運算簡化。
• 此模式廣泛適用於「不動核心代碼、對外暴露統一介面」的資料庫重構場景。