SQL Server 觸發器完整操作指南：創建、修改、刪除、查看、魔法表與 6 大實戰案例

觸發器核心機制

• 特殊存儲過程，事件驅動自動執行，不可顯式調用。
• 兩張魔法表（內存駐留、結構同基表）：
  inserted：INSERT/UPDATE 的新行。
  deleted：DELETE/UPDATE 的舊行。
  UPDATE = 先進 deleted（舊值），再進 inserted（新值）。
• 分類：
  AFTER：動作後執行，約束已檢查，僅表，可多個。
  INSTEAD OF：替代動作執行，動作本身不執行，表/視圖均可，每事件僅一個。

語法速查

-- 創建
CREATE TRIGGER trg_Name
ON TableOrView
{ AFTER | INSTEAD OF } { INSERT, UPDATE, DELETE }
AS BEGIN ... END;

-- 修改
ALTER TRIGGER trg_Name ... (同創建)

-- 刪除
DROP TRIGGER trg_Name;

-- 查看所有觸發器
SELECT name, parent_class_desc, create_date, modify_date
FROM sys.triggers;

-- 查看單個定義
EXEC sp_helptext 'trg_Name';
-- 或
SELECT OBJECT_DEFINITION(OBJECT_ID('trg_Name'));

執行順序與約束關系

1. INSTEAD OF 觸發器（最早，可預處理/攔截）。
2. 約束檢查（PK/FK/CK/DEFAULT）。
3. AFTER 觸發器（按 sp_settriggerorder 設置首/尾/無序）。

若約束違反，AFTER 觸發器不執行；INSTEAD OF 可在約束前處理數據使其通過。

6 大實戰案例

1. 業務攔截：商品整理中禁止下單

CREATE TRIGGER trg_Order_BlockOnProcessing
ON Orders
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN Goods g ON g.Name = i.GoodsName
        WHERE g.Status = 1 -- 整理中
    )
    BEGIN
        RAISERROR('商品整理中，無法下單', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;

2. 庫存扣減：下單自動扣減庫存

CREATE TRIGGER trg_Order_DeductStock
ON Orders
AFTER INSERT
AS
BEGIN
    UPDATE g
    SET g.Stock = g.Stock - i.Qty
    FROM Goods g
    JOIN inserted i ON g.Name = i.GoodsName;
END;

3. 級聯刪除：刪除商品同步刪除訂單

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

4. 字段保護：訂單日期不可人工修改

CREATE TRIGGER trg_Order_ProtectDate
ON Orders
AFTER UPDATE
AS
BEGIN
    IF UPDATE(OrderDate)
    BEGIN
        RAISERROR('訂單日期不可修改', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

5. 完整性檢查：下單商品必須存在

CREATE TRIGGER trg_Order_ValidateGoods
ON Orders
AFTER INSERT
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM inserted i
        LEFT JOIN Goods g ON g.Name = i.GoodsName
        WHERE g.Name IS NULL
    )
    BEGIN
        RAISERROR('商品不存在', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

6. 異構同步：訂單插入同步至異構表

ALTER TRIGGER trg_Order_SyncExternal
ON Orders
FOR INSERT
AS
BEGIN
    INSERT INTO ExternalOrder (Id, GoodsName, Qty)
    SELECT Id, GoodsName, Qty FROM inserted;
END;

性能與維護紅線

• 🚫 觸發器中禁止：發郵件、調 Web Service、長事務、複雜計算、游標循環。
• ✅ 觸發器應：單語句集合操作、極簡邏輯、設置 SET NOCOUNT ON。
• 🔍 監控：sys.dm_exec_trigger_stats 統計執行次數/耗時，定期審計。
  
• 🔄 替代方案：CDC (Change Data Capture) 異步捕獲變更 → 消息隊列 → 下游消費，解耦、可重試、不阻塞主事務。