SQL Server 存儲過程與觸發器全解析：分類、差異與核心機制

存儲過程：封裝業務邏輯的核心單元

存儲過程是預編譯的 T-SQL 批次，存儲於數據庫中，支持參數輸入/輸出、流程控制、錯誤處理與事務管理，是實現數據層業務封裝的首選機制。

三大類型劃分

1. 系統存儲過程：前綴 sp_，存儲於 master 數據庫，提供實例級管理功能（如 sp_who、sp_help），調用時無需指定數據庫前綴。
2. 用戶存儲過程：開發者創建，完成特定業務功能，駐留於用戶數據庫，命名建議避免 sp_ 前綴以防解析開銷。
3. 擴展存儲過程：前綴 xp_，通過外部 DLL 實現（如 xp_cmdshell），可訪問作業系統資源，安全風險極高，生產環境建議禁用或嚴格限權。

觸發器：事件驅動的自動執行單元

觸發器綁定於表/視圖的 DML（INSERT/UPDATE/DELETE）或 DDL（CREATE/ALTER/DROP）事件，無法顯式調用，由數據庫引擎在事件發生時自動激活。

核心差異：調用方式與事務上下文

兩類觸發器對比

• AFTER 觸發器（FOR 觸發器）：事件動作完成後執行，約束檢查已通過。僅適用於表，每表每事件可創建多個，可通過 sp_settriggerorder 指定首尾順序。
• INSTEAD OF 觸發器：替代觸發動作執行，動作本身不執行。適用於表與視圖，每表每事件僅能創建一個。常用於實現視圖更新、跨表級聯邏輯、業務攔截校驗。

魔法表：inserted 與 deleted

SQL Server 為每個觸發器執行上下文創建兩張內存驻留的邏輯表，結構與基表完全一致：

• inserted：包含 INSERT 或 UPDATE 操作的新行（UPDATE 視為刪除舊行+插入新行）。
• deleted：包含 DELETE 或 UPDATE 操作的舊行。

關鍵規則：

1. 觸發器內只能讀取，不可修改兩表。
2. 觸發器結束時自動銷毀。
3. UPDATE 操作會同時填充兩表：deleted 存舊值，inserted 存新值。

典型應用場景代碼：級聯刪除觸發器

CREATE TRIGGER trg_Goods_CascadeDelete
ON Goods
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE o
    FROM Orders o
    INNER JOIN deleted d ON o.GoodsName = d.Name;
END;

性能與維護警示

• ⚠️ 觸發器隱式執行：難以調試、易產生連鎖觸發、增加事務持鎖時間，導致並發下降。
• ⚠️ 避免在觸發器中放置耗時邏輯：發郵件、調用 Web Service、複雜計算均應異步化（如 Service Broker、消息隊列）。
• ✅ 優先使用約束：主鍵、外鍵、CHECK、DEFAULT 約束由引擎內部優化，性能遠優於觸發器實現同樣邏輯。
• ✅ 審計/日誌記錄：觸發器適合輕量級審計，大量審計建議使用 CDC（Change Data Capture）或擴展事件。

最佳實踐清單

1. 業務邏輯封裝 → 存儲過程（顯式調用、可測試、可版本控制）。
2. 跨表完整性/審計/視圖更新 → INSTEAD OF 觸發器（謹慎使用）。
3. 事後處理/衍生數據維護 → AFTER 觸發器（保持輕量、避免事務過長）。
4. 禁用 xp_cmdshell 等高危擴展存儲過程，採用 CLR 集成或外部任務隊列替代。
5. 定期檢查 sys.triggers、sys.procedures，清理廢棄對象。