萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 儲存程序與觸發器:分類、差異、INSERTED/DELETED 表與最佳實務

一、儲存程序分類

類型前綴儲存位置用途
系統儲存程序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

  • 邏輯表,存在記憶體,唯讀,觸發器結束自動銷毀
  • 結構與觸發資料表完全一致

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
操作INSERTEDDELETED
INSERT新增列空
DELETE空刪除列
UPDATE新值舊值