SQL 觸發器中的 deleted 與 inserted 表詳解

概述

在 SQL Server 觸發器中，inserted 和 deleted 是兩張特殊的**虛擬表**（概念表），僅存在於觸發器執行期間，分別保存 DML 操作前後的資料快照。它們不佔用實體儲存空間，讀取效能極高。

資料內容對照

典型應用場景

1. 稽核與日誌記錄

CREATE TRIGGER trg_Audit_Employee
ON dbo.Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO dbo.Employee_Audit (EmpID, ActionType, ChangedData, ChangeTime)
    SELECT 
        COALESCE(i.EmpID, d.EmpID),
        CASE WHEN i.EmpID IS NULL THEN 'DELETE' 
             WHEN d.EmpID IS NULL THEN 'INSERT' 
             ELSE 'UPDATE' END,
        (SELECT i.* FOR JSON AUTO), -- 或自訂格式
        GETDATE()
    FROM inserted i
    FULL JOIN deleted d ON i.EmpID = d.EmpID;
END;

2. 實作複雜業務規則（如跨表驗證）

CREATE TRIGGER trg_Check_Order_Amount
ON dbo.OrderDetail
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN dbo.[Order] o ON i.OrderID = o.OrderID
        GROUP BY o.OrderID
        HAVING SUM(i.LineTotal) > o.CreditLimit
    )
    BEGIN
        RAISERROR('訂單明細總額超過信用額度', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

3. 維護衍生/彙總資料

CREATE TRIGGER trg_Update_Product_Sales
ON dbo.SalesOrderDetail
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE p
    SET TotalQtySold = ISNULL(p.TotalQtySold, 0) + ISNULL(i.QtyDelta, 0) - ISNULL(d.QtyDelta, 0)
    FROM dbo.Product p
    JOIN (
        SELECT ProductID, SUM(OrderQty) AS QtyDelta FROM inserted GROUP BY ProductID
    ) i ON p.ProductID = i.ProductID
    LEFT JOIN (
        SELECT ProductID, SUM(OrderQty) AS QtyDelta FROM deleted GROUP BY ProductID
    ) d ON p.ProductID = d.ProductID;
END;

效能與最佳實務

• 避免在觸發器中執行耗時操作（如發送郵件、呼叫 Web Service），改用非同步機制（Service Broker、資料表佇列 + Agent Job）。
• 使用 SET NOCOUNT ON 減少網路往返。
• 注意多列更新：UPDATE(col) 或 COLUMNS_UPDATED() 判斷特定欄位是否被修改，避免不必要邏輯。
• 觸發器巢狀層級預設最多 32 層，可透過 sp_configure 'nested triggers' 關閉。
• INSTEAD OF 觸發器 可攔截並替換原始 DML，常用於視圖更新或複雜驗證。

常見誤區

1. 誤以為 inserted/deleted 有索引——它們是掃描存取，大量資料時效能下降快。
2. 在觸發器中對同一資料表再次發動相同 DML，導致無限遞迴（需設定 RECURSIVE_TRIGGERS 或自行防禦）。
3. 忽略 MERGE 陳述式同時產生 INSERT/UPDATE/DELETE 三種動作，邏輯需涵蓋三種情況。

除錯技巧

• 在觸發器開頭加入 PRINT 'TRIGGER FIRED'; SELECT * FROM inserted; SELECT * FROM deleted; 快速觀察資料流向。
• 使用 sys.dm_exec_trigger_stats 監控觸發器執行頻率與耗時。