一、儲存程序分類
| 類型 | 前綴 | 儲存位置 | 用途 |
| 系統儲存程序 | sp_ | master | 系統管理、中繼資料查詢 |
| 使用者儲存程序 | 自訂 | 使用者資料庫 | 業務邏輯封裝 |
| 擴充儲存程序 | xp_ | master (DLL) | 外部程式碼整合 (已棄用,改用 CLR) |
二、觸發器
定義
特定事件 (INSERT/UPDATE/DELETE) 發生時自動執行的特殊儲存程序,無法直接呼叫。
觸發器 vs 儲存程序
| 特性 | 儲存程序 | 觸發器 |
| 呼叫方式 | 顯式呼叫 (EXEC) | 事件自動觸發 |
| 參數 | 支援輸入/輸出參數 | 無參數 |
| 回傳值 | 可回傳整數/結果集 | 無回傳值 |
| 交易控制 | 呼叫者控制 | 隱含在觸發交易中 |
| 巢狀/遞迴 | 支援 | 可設定巢狀層級 |
觸發器分類
- DML 觸發器:資料操作事件 (INSERT/UPDATE/DELETE)
- DDL 觸發器:結構變更事件 (CREATE/ALTER/DROP)
- 登入觸發器:LOGON 事件
每張表最多三類 DML 觸發器
- INSERT 觸發器
- UPDATE 觸發器
- DELETE 觸發器
每類可建立多個,須指定執行順序 (sp_settriggerorder)。
三、兩個神奇表:INSERTED 與 DELETED
- 邏輯表,存在記憶體,唯讀,觸發器結束自動銷毀
- 結構與觸發資料表完全一致
| 操作 | INSERTED | DELETED |
| INSERT | 新增列 | 空 |
| DELETE | 空 | 刪除列 |
| UPDATE | 新值 | 舊值 |
UPDATE 本質:先 DELETE 舊列 → 再 INSERT 新列。因此 UPDATE 同時填充兩表。
四、觸發器範本
CREATE TRIGGER tr_TableName_Action
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- 判斷操作類型
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
PRINT 'UPDATE';
ELSE IF EXISTS (SELECT 1 FROM inserted)
PRINT 'INSERT';
ELSE
PRINT 'DELETE';
-- 業務邏輯:如稽核、同步、驗證
-- INSERT INTO AuditLog ... SELECT ..., GETDATE(), SYSTEM_USER FROM inserted;
END;
五、效能與最佳實務警示
- 避免在觸發器中執行耗時操作:郵件、Web Service、大量資料處理 → 改用 Service Broker/異步佇列
- 觸發器巢狀深度預設 32 層,超過將報錯,慎用遞迴
- 使用
SET NOCOUNT ON 減少網路封包
- 避免在觸發器中修改同一表,易造成無限迴圈
- 測試觸發器對大量批次操作的影響:
INSERT ... SELECT 100萬列 會逐列觸發
總結
儲存程序是主動式封裝,觸發器是被動式攔截。善用 INSERTED/DELETED 實作稽核與驗證,但務必克制觸發器邏輯複雜度,維護資料庫核心效能。
copyright ©
萬盛學電腦網 all rights reserved