SQL Server 觸發器多筆資料處理實戰：正確使用 Inserted/Deleted 表與游標

許多開發者誤以為 SQL Server 觸發器僅能處理單筆資料，實際上 INSERTED 與 DELETED 兩張內部記憶體表會完整保存該 DML 陳述式影響的所有資料列。本文透過實例示範如何在 DELETE 觸發器中使用游標逐筆讀取 DELETED 表資料，將刪除記錄轉存至稽核表，確保批次刪除不遺漏任何資料。

環境建立

-- 建立主表與稽核表
CREATE TABLE Table1 ([ID] INT PRIMARY KEY, [Value] VARCHAR(100));
CREATE TABLE Table2 ([ID] INT PRIMARY KEY, [Value] VARCHAR(100));

-- 插入 100 筆測試資料
DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
    INSERT INTO Table1 ([ID], [Value]) VALUES (@i, CAST(@i AS VARCHAR));
    SET @i = @i + 1;
END

建立 DELETE 觸發器

CREATE TRIGGER TR_D_Table1 ON Table1
FOR DELETE
AS
BEGIN
    DECLARE @ID INT, @Value VARCHAR(100);

    -- 宣告區域前進游標，效能優於全域游標
    DECLARE Cur_Del CURSOR LOCAL FORWARD_ONLY
    FOR SELECT [ID], [Value] FROM DELETED;

    OPEN Cur_Del;
    FETCH NEXT FROM Cur_Del INTO @ID, @Value;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO Table2 ([ID], [Value]) VALUES (@ID, @Value);
        FETCH NEXT FROM Cur_Del INTO @ID, @Value;
    END

    CLOSE Cur_Del;
    DEALLOCATE Cur_Del;
END;

驗證結果

-- 執行批次刪除
DELETE FROM Table1;

-- 查詢稽核表，應有 100 筆資料
SELECT COUNT(*) FROM Table2;  -- 回傳 100

關鍵觀念釐清

效能與最佳實務建議

• 優先使用集合式寫法：若邏輯允許，直接 INSERT INTO Table2 SELECT ID, Value FROM DELETED 效能遠優於游標。
• 游標選項：加上 LOCAL FORWARD_ONLY 減少鎖定與記憶體負擔。
• 避免觸發器巢狀過深：觸發器內再觸發其他觸發器，預設最多 32 層，超過將報錯。
• 大量資料考量：百萬筆以上批次操作建議改用 OUTPUT 子句或 CDC（異動資料擷取）機制。

延伸閱讀

• INSTEAD OF 觸發器可攔截 DML 並自訂處理邏輯，適用於視圖更新、跨表業務規則。
• SQL Server 2016+ 可搭配 SESSION_CONTEXT 在觸發器間傳遞資訊。