SQL Server 觸發器深度實戰：分類、魔術表、六大範本與最佳實踐

觸發器核心概念

• 本質：特殊存儲過程，**由事件自動觸發**，不可由名稱直接呼叫。
• 分類：
  • DML 觸發器：INSERT/UPDATE/DELETE → AFTER (預設) / INSTEAD OF
  • DDL 觸發器：CREATE/ALTER/DROP 等結構變更 (SQL 2005+)
• 兩張魔術表 (唯讀、記憶體駐留、結構與基表相同)：
  • inserted：新增/修改後的資料
  • deleted：刪除/修改前的資料
• 交易語境：觸發器執行於觸發語句的**同一交易**中，可 ROLLBACK 撤銷觸發操作。

建立語法速查

CREATE TRIGGER trigger_name
ON table_or_view_name
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
BEGIN
    -- T-SQL 邏輯
END;

六大實戰範本

1. AFTER INSERT：聯動新增明細

CREATE TRIGGER tgr_classes_insert ON classes
AFTER INSERT
AS
BEGIN
    DECLARE @id INT, @name VARCHAR(20), @temp INT;
    SELECT @id = id, @name = name FROM inserted;
    SET @name = @name + CAST(@id AS VARCHAR);
    SET @temp = @id / 2;
    INSERT INTO student (name, age, score, cid) VALUES (@name, 18 + @id, @temp, @id);
    PRINT '學生聯動新增成功';
END;

2. AFTER DELETE：刪除前備份

CREATE TRIGGER tgr_classes_delete ON classes
AFTER DELETE
AS
BEGIN
    PRINT '備份中...';
    IF OBJECT_ID('classesBackup', 'U') IS NOT NULL
        INSERT INTO classesBackup SELECT name, createDate FROM deleted;
    ELSE
        SELECT * INTO classesBackup FROM deleted;
    PRINT '備份完成';
END;

3. AFTER UPDATE：級聯修改關聯表

CREATE TRIGGER tgr_classes_update ON classes
AFTER UPDATE
AS
BEGIN
    DECLARE @oldName VARCHAR(20), @newName VARCHAR(20);
    SELECT @oldName = name FROM deleted;
    IF EXISTS (SELECT 1 FROM student WHERE name LIKE '%' + @oldName + '%')
    BEGIN
        SELECT @newName = name FROM inserted;
        UPDATE student SET name = REPLACE(name, @oldName, @newName)
        WHERE name LIKE '%' + @oldName + '%';
        PRINT '級聯修改完成';
    END
    ELSE PRINT '無需修改 student';
END;

4. 列級 UPDATE 觸發：保護關鍵欄位

CREATE TRIGGER tgr_classes_update_column ON classes
AFTER UPDATE
AS
BEGIN
    IF UPDATE(createDate) -- 僅當 createDate 被修改時觸發
    BEGIN
        RAISERROR('系統提示：班級建立時間不可修改！', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

5. INSTEAD OF DELETE：自訂刪除邏輯 (可用於視圖)

CREATE TRIGGER tgr_classes_insteadOf ON classes
INSTEAD OF DELETE
AS
BEGIN
    DECLARE @id INT, @name VARCHAR(20);
    SELECT @id = id, @name = name FROM deleted;
    DELETE FROM student WHERE cid = @id;      -- 先刪子表
    DELETE FROM classes WHERE id = @id;       -- 再刪主表
    PRINT '級聯刪除完成：' + @name;
END;

6. 審計日誌觸發器：記錄增刪改操作

CREATE TABLE log (id INT IDENTITY PRIMARY KEY, action VARCHAR(20), createDate DATETIME DEFAULT GETDATE());

CREATE TRIGGER tgr_student_log ON student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        INSERT INTO log(action) VALUES ('UPDATED');
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        INSERT INTO log(action) VALUES ('INSERTED');
    ELSE IF EXISTS (SELECT 1 FROM deleted)
        INSERT INTO log(action) VALUES ('DELETED');
END;

管理與除錯指令

效能與設計鐵律

1. 輕量化：觸發器內**嚴禁**耗時運算、跨資料庫呼叫、發送郵件、呼叫 Web Service。
2. 集合思維：inserted/deleted 可能包含多行，**禁止**假設單行 (如 `SELECT @id = id FROM inserted`)，需用 `JOIN`/`EXISTS`/集合操作。
3. 避免巢狀觸發：預設允許巢狀 32 層，建議 `sp_configure 'nested triggers', 0` 關閉，或謹慎設計防止無限遞迴。
4. 交易範圍：觸發器錯誤導致 `ROLLBACK` 會回滾**整個觸發批次**，外層呼叫端需捕獲錯誤 (TRY...CATCH)。
5. INSTEAD OF 唯一性：同一張表/視圖同一事件僅能建立一個 INSTEAD OF 觸發器。
6. 文檔化：在 `WITH ENCRYPTION` 外，務必在擴展屬性 (`sp_addextendedproperty`) 記錄業務用途、作者、變更歷程。

總結

觸發器是資料庫端**被動式業務邏輯**的利器，適用於稽核、衍生欄位維護、跨表一致性、軟刪除等場景。掌握 inserted/deleted 集合語義、AFTER vs INSTEAD OF 差異、列級 `UPDATE()` 判斷，並遵守「輕量、集合、無巢狀、有文檔」四原則，即能寫出健壯可維護的觸發器。