SQL Server 觸發器 (Trigger) 完整建立指南

觸發器是針對特定表的 INSERT、UPDATE、DELETE 操作自動執行的預存程序。本文說明語法、虛擬表 inserted/deleted、以及實戰業務邏輯範例。

建立觸發器語法

CREATE TRIGGER trigger_name
ON table_name
FOR INSERT | DELETE | UPDATE  -- 或 AFTER INSERT, INSTEAD OF UPDATE
AS
BEGIN
    -- 觸發器邏輯
    -- 可使用 inserted (新資料) 與 deleted (舊資料) 虛擬表
END;

虛擬表說明

實戰範例：庫存同步與業務驗證

建立範例表

CREATE TABLE ta1 (
    taid INT IDENTITY(1001,1) PRIMARY KEY,
    taname VARCHAR(20) NOT NULL,
    tasl INT  -- 庫存數量
);

CREATE TABLE ta2 (
    ta2id INT IDENTITY(1001,1) PRIMARY KEY,
    ta2name VARCHAR(20) NOT NULL,
    ta2ysh INT,  -- 預警閾值
    ta2sl INT    -- 同步庫存
);

建立 UPDATE 觸發器

CREATE TRIGGER tru_ta1
ON ta1
FOR UPDATE
AS
BEGIN
    -- 檢查是否有 tasl 被修改
    IF UPDATE(tasl)
    BEGIN
        -- 業務規則：ta1.tasl 不得大於 ta2.ta2ysh
        IF EXISTS (
            SELECT 1 FROM ta2 
            JOIN inserted i ON ta2.ta2id = i.taid
            WHERE ta2.ta2ysh < i.tasl
        )
        BEGIN
            RAISERROR('庫存超過預警閾值，拒絕更新', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END
        
        -- 同步更新 ta2
        UPDATE ta2
        SET ta2sl = i.tasl
        FROM ta2
        JOIN inserted i ON ta2.ta2id = i.taid;
    END
END;

關鍵最佳實踐

• 使用 IF UPDATE(col)：僅在特定欄位變更時執行邏輯，減少開銷
• 集合思維：觸發器一次可能處理多行，避免寫單行處理邏輯
• 避免巢狀觸發：sp_configure 'nested triggers', 0 或謹慎設計
• 錯誤處理：使用 TRY...CATCH、ROLLBACK、RAISERROR/THROW
• 效能考量：觸發器在交易內執行，複雜邏輯會拉長鎖定時間

管理指令

-- 查看觸發器
SELECT * FROM sys.triggers WHERE parent_id = OBJECT_ID('ta1');

-- 查看定義
EXEC sp_helptext 'tru_ta1';

-- 停用/啟用
DISABLE TRIGGER tru_ta1 ON ta1;
ENABLE TRIGGER tru_ta1 ON ta1;

-- 刪除
DROP TRIGGER tru_ta1;

MySQL 觸發器差異補充

MySQL 觸發器語法不同：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW  -- MySQL 必須逐列
BEGIN
    -- NEW.col / OLD.col 存取
END;

MySQL 不支援 INSTEAD OF 觸發器，也不支援陳述式級觸發器。