SQL Server 觸發器深度解析：AFTER vs INSTEAD OF、級聯邏輯與效能權衡

觸發器是綁定在表/視圖上的特殊存儲過程，由 DML 事件（INSERT/UPDATE/DELETE）自動觸發，用於實現複雜參考完整性、審計追蹤、級聯業務邏輯、跨庫同步等約束無法覆蓋的場景。本文系統梳理兩大類型、執行機制、級聯風險與效能優化要點。

核心價值：約束做不到的事

• 跨表複雜驗證：如「訂單金額不得超過客戶信用額度」，需查詢客戶表聚合值。
• 審計日誌：記錄修改前後值、操作用戶、時間、應用上下文。
• 級聯業務：刪除主訂單自動刪除明細、更新庫存、發送通知。
• 外部調用：CLR 觸發器調用 Web Service、寫入消息隊列、觸發 ETL。

兩大類型對比

虛擬表結構與數據流

• inserted：INSERT/UPDATE 的新值；DELETE 時為空。
• deleted：DELETE/UPDATE 的舊值；INSERT 時為空。
• UPDATE 視為「先刪後插」，兩表均有數據，行數相等，可 JOIN 比對變化列。

級聯觸發風險與控制

觸發器內操作其他表若又觸發觸發器，形成級聯鏈。SQL Server 默認允許巢狀觸發（最多 32 級），可用 sp_configure 'nested triggers', 0 關閉。

風險

• 無限遞歸：表 A 觸發器更新表 B，表 B 觸發器又更新表 A。
• 事務膨脹：頂層事務包含所有級聯操作，鎖持有時間長、日誌暴增、死鎖概率增。
• 效能雪崩：單條 INSERT 引發連鎖反應，延遲不可控。

控制手段

1. TRIGGER_NESTLEVEL()：檢測巢狀層級，超限直接 RETURN。
2. 上下文標誌位：SET CONTEXT_INFO 0x1 標記「已由觸發器處理」，下層觸發器檢測到則跳過。
3. 改用異步：觸發器只寫入「待處理隊列表」，Service Broker/外部 Worker 消費執行級聯邏輯，縮短關鍵路徑。

效能優化鐵律

1. 避免在觸發器中查詢大表/跨庫：虛擬表在內存，但引用表可能觸發物理讀。必要時建立覆蓋索引。
2. 集合化處理：嚴禁逐行游標。用 INSERT INTO Log SELECT ... FROM inserted 批量寫入。
3. 最小化事務範圍：觸發器隱式包含在觸發語句事務中，邏輯越簡單越好。
4. 禁用不必要的觸發器：DISABLE TRIGGER trig_name ON table_name，大批量導入時關閉，導入後重開。
5. 監控指標：sys.dm_exec_trigger_stats 關注 total_worker_time、total_logical_reads、execution_count。

最佳實踐決策樹

1. 能用 約束 (PK/FK/CHECK) 解決？→ 用約束（引擎級、最快）。
2. 能用 計算列/索引視圖 衍生？→ 用計算列/視圖（聲明式、自動維護）。
3. 需攔截並替換原動作（如視圖更新）？→ INSTEAD OF。
4. 需動作後副作用（審計、級聯、通知）？→ AFTER，並遵循上述效能鐵律。
5. 級聯邏輯複雜/耗時？→ 觸發器只入隊，異步消費。