MySQL 5.0 新特性教程：觸發器入門（語法、權限、限制、實戰模式）

為何需要觸發器？

• 早期用戶長期呼聲
• 符合 ANSI SQL 標準
• 資料進庫前攔截驗證/轉換（防髒資料）
• 可修改/取消 INSERT、UPDATE、DELETE 行為
• 會話級監控資料變更

語法結構

CREATE TRIGGER 
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON 
FOR EACH ROW

六大要素解析

函數級限制同樣適用

觸發器體內禁止：DDL (CREATE/ALTER/DROP)、事務控制 (COMMIT/ROLLBACK/SAVEPOINT)、鎖表、權限變更、SELECT FROM table 等。允許：流程控制 (IF/CASE/LOOP)、變量 (DECLARE/SET)、條件/異常聲明。

權限模型

目前需 ROOT 權限 或極高特權建立觸發器。未來版本將引入獨立 `CREATE TRIGGER` 權限：

GRANT CREATE TRIGGER ON table_name TO user_list;
REVOKE CREATE TRIGGER ON table_name FROM user_list;

實戰模式範例

1. 資料驗證與轉換 (BEFORE INSERT/UPDATE)

CREATE TRIGGER users_bi BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
    SET NEW.created_at = NOW();
END;

2. 審計日誌 (AFTER UPDATE/DELETE)

CREATE TRIGGER orders_ad AFTER DELETE ON orders
FOR EACH ROW
INSERT INTO audit_log (table_name, action, old_data, changed_at)
VALUES ('orders', 'DELETE', CONCAT('id=', OLD.id), NOW());

避坑指南

• 觸發器內異常會導致原語句回滾，慎用 `SIGNAL`
• 避免觸發器鏈式調用（觸發器修改另一表又觸發該表觸發器）
• 大批量操作時觸發器開銷顯著，考慮應用層批次處理
• 備份/還原時注意 `mysqldump --triggers` 參數