MySQL 觸發器完整操作指南（創建、查看、刪除）

觸發器核心概念

觸發器是與表綁定的數據庫對象，由 INSERT/UPDATE/DELETE 事件激活自動執行。不支持 SELECT、TRUNCATE、DDL 觸發。常用於：

• 審計日誌（記錄變更前後值）
• 派生數據維護（如統計計數、冗余字段同步）
• 業務規則強制執行（跨表約束）

創建語法

單語句觸發器

CREATE TRIGGER trigger_name
BEFORE | AFTER trigger_event
ON table_name
FOR EACH ROW
trigger_stmt;

多語句觸發器

CREATE TRIGGER trigger_name
BEFORE | AFTER trigger_event
ON table_name
FOR EACH ROW
BEGIN
    stmt1;
    stmt2;
END;

關鍵點

• BEFORE：行變更前執行（可修改 NEW 值）
• AFTER：行變更後執行（不可修改 NEW）
• FOR EACH ROW：行級觸發（MySQL 僅支持行級）
• 命名建議：trg_{table}_{event}_{timing} 如 trg_user_insert_after
• 分隔符處理：DELIMITER $$ ... END $$ DELIMITER ;

實戰示例

1. 審計日誌：記錄部門表變更時間

DELIMITER $$
CREATE TRIGGER trg_dept_audit_after
AFTER INSERT ON t_dept
FOR EACH ROW
BEGIN
    INSERT INTO t_audit_log (table_name, action, log_time)
    VALUES ('t_dept', 'INSERT', NOW());
END $$
DELIMITER ;

2. 派生字段維护：同步部門員工數

DELIMITER $$
CREATE TRIGGER trg_emp_count_after_insert
AFTER INSERT ON t_employee
FOR EACH ROW
BEGIN
    UPDATE t_dept SET emp_count = emp_count + 1 WHERE deptno = NEW.deptno;
END $$

CREATE TRIGGER trg_emp_count_after_delete
AFTER DELETE ON t_employee
FOR EACH ROW
BEGIN
    UPDATE t_dept SET emp_count = emp_count - 1 WHERE deptno = OLD.deptno;
END $$
DELIMITER ;

查看觸發器

-- 方法1：SHOW 命令
SHOW TRIGGERS\G;

-- 方法2：信息模式表（可篩選）
USE information_schema;
SELECT * FROM TRIGGERS WHERE TRIGGER_SCHEMA='mydb'\G;
SELECT * FROM TRIGGERS WHERE TRIGGER_NAME='trg_dept_audit_after'\G;

刪除觸發器

DROP TRIGGER [IF EXISTS] trigger_name;

NEW/OLD 伪行訪問規則

限制與注意事項

1. 不能調用存儲過程/函數（除非 NO SQL）
2. 不能使用 START TRANSACTION/COMMIT/ROLLBACK
3. 觸發器內異常會導致原語句回滾
4. 避免觸發器鏈式調用（A 表觸發器改 B 表，B 表有觸發器改 A 表）
5. 大批量導入時考慮禁用觸發器：SET SESSION sql_log_bin=0; (需 SUPER 權限)

性能影響評估

觸發器增加寫入延遲，每行額外執行邏輯。高併發寫入表建議：

• 將異步處理移至應用層或消息隊列
• 使用 AFTER 而非 BEFORE 減少鎖持有時間
• 觸發器內避免複雜查詢、子查詢