MySQL 觸發器完全指南：語法、變量、NEW/OLD、執行順序與實戰案例

MySQL 觸發器完全指南：語法、變量、NEW/OLD、執行順序與實戰案例

觸發器是與表綁定的特殊存儲程序，由 DML 事件 (INSERT/UPDATE/DELETE) 自動激活。本文系統講解創建語法、六種類型、變量作用域、NEW/OLD 伪表、執行順序與事務交互、管理命令及經典實戰場景。

一、創建語法

CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

• trigger_time：BEFORE | AFTER
• trigger_event：INSERT | UPDATE | DELETE
• 共 6 種組合，單表同類型僅能創建一個，最多 6 個。
• trigger_stmt：單條 SQL 或 BEGIN ... END 塊。

二、觸發事件擴展

• LOAD DATA 等同批量 INSERT，觸發 INSERT 型觸發器。
• REPLACE：若主鍵/唯一鍵衝突，先 DELETE 再 INSERT，觸發 DELETE + INSERT 型觸發器。

三、BEGIN ... END 與 DELIMITER

DELIMITER $$
CREATE TRIGGER tri_stuInsert AFTER INSERT ON student
FOR EACH ROW
BEGIN
    DECLARE c INT;
    SET c = (SELECT stuCount FROM class WHERE classID = NEW.classID);
    UPDATE class SET stuCount = c + 1 WHERE classID = NEW.classID;
END$$
DELIMITER ;

• DELIMITER $$ 臨時修改語句結束符，避免內部分號被誤判為結束。
• 塊內語句必須以分號結尾，整個塊以 $$ 結尾。

四、變量聲明與賦值

DECLARE var_name [, ...] type [DEFAULT value];
SET var_name = expr [, var_name = expr] ...

• 局部變量僅在 BEGIN ... END 內有效，無 @ 前綴。
• 會話變量 (@var) 跨觸發器可見，慎用避免污染。

五、NEW 與 OLD 核心規則

引用方式：NEW.col_name、OLD.col_name。

六、執行順序與事務交互 (InnoDB)

1. BEFORE 觸發器執行
2. DML 語句執行 (若 BEFORE 失敗，DML 不執行)
3. AFTER 觸發器執行 (若 DML 失敗，AFTER 不觸發)
4. 若 AFTER 失敗，整個事務回滾 (含 DML 及 BEFORE 修改)

⚠️ 觸發器內不能顯式提交/回滾事務，也不能調用會隱式提交的語句 (如 LOCK TABLES、DDL)。

七、管理命令

SHOW TRIGGERS [FROM db_name];
DROP TRIGGER [IF EXISTS] [db_name.]trigger_name;

八、實戰案例：審計日志

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64),
    operation ENUM('INSERT','UPDATE','DELETE'),
    old_data JSON,
    new_data JSON,
    op_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER trg_user_audit AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data)
    VALUES ('user', 'UPDATE', 
        JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'email', OLD.email),
        JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'email', NEW.email)
    );
END$$
DELIMITER ;

九、避坑指南

• 觸發器邏輯過重拖慢寫入，核心鏈路慎用。
• 避免觸發器鏈式調用 (觸發器修改表再觸發其他觸發器)，極難排查。
• 備份恢復時注意 --triggers 參數，mysqldump 默認包含觸發器。