MySQL 觸發器完整指南：創建、變量、NEW/OLD、執行順序與實戰避坑

觸發器基礎語法

CREATE TRIGGER `trigger_name`
trigger_time          -- BEFORE | AFTER
trigger_event         -- INSERT | UPDATE | DELETE
ON `tbl_name`
FOR EACH ROW
trigger_stmt          -- 單條 SQL 或 BEGIN ... END 塊

6 種組合：BEFORE INSERT/UPDATE/DELETE、AFTER INSERT/UPDATE/DELETE。
限制：同一表同一時機同一事件**只能創建一個觸發器**，最多 6 個。

觸發事件擴展

BEGIN ... END 塊與 DELIMITER

DELIMITER $  -- 修改語句結束符，避免內部 ; 衝突
CREATE TRIGGER `tri_stu_insert` 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 ;  -- 恢復默認

局部變量聲明與賦值

DECLARE var_name [, var_name]... type [DEFAULT value];
-- 必須在 BEGIN 後、其他語句前聲明
SET var_name = expr [, var_name = expr]...;

NEW 與 OLD 核心差異

用法：NEW.col_name、OLD.col_name。
妙用：BEFORE INSERT 修改 NEW.id = CONCAT('2024', NEW.id) 實現自定義主鍵生成，不再觸發遞歸。

查看與刪除

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

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

1. BEFORE 觸發器失敗 → SQL 不執行，事務不啟動/回滾。
2. SQL 執行失敗 (約束衝突、死鎖) → AFTER 觸發器**不觸發**。
3. AFTER 觸發器失敗 → SQL **回滾**，整個事務無效。

結論：觸發器與觸發語句在**同一事務**中，原子性綁定。

實戰案例：審計日志表

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,
    `operator` VARCHAR(64),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $
CREATE TRIGGER `audit_user_insert` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`,`operation`,`new_data`,`operator`)
    VALUES ('users','INSERT',CAST(NEW AS JSON),CURRENT_USER());
END$

CREATE TRIGGER `audit_user_update` AFTER UPDATE ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`,`operation`,`old_data`,`new_data`,`operator`)
    VALUES ('users','UPDATE',CAST(OLD AS JSON),CAST(NEW AS JSON),CURRENT_USER());
END$

CREATE TRIGGER `audit_user_delete` AFTER DELETE ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`,`operation`,`old_data`,`operator`)
    VALUES ('users','DELETE',CAST(OLD AS JSON),CURRENT_USER());
END$
DELIMITER ;

避坑指南

1. 避免觸發器鏈式調用：A 表觸發器修改 B 表 → B 表觸發器修改 A 表 → 死循環/遞歸超限。
2. 性能殺手：高頻寫入表加觸發器 → 雙倍寫入開銷。審計日志建議異步寫入 (Binlog 解析 / Canal / Kafka)。
3. 備份還原陷阱：mysqldump --triggers 導出，還原時觸發器可能在數據導入前創建，導致報錯。建議單獨導出觸發器 SQL，數據導入後再創建。
4. 版本差異：5.7+ 支援多語句觸發器無需 DELIMITER (客戶端支援時)，但為兼容性保留。
5. 權限檢查：觸發器按 DEFINER 權限執行，若 DEFINER 被刪除/權限變更，觸發器執行報錯。指定 DEFINER = CURRENT_USER 或固定運維賬號。

總結

觸發器是「數據庫主動防禦線」，適合**審計、同步衍生數據、強制業務規則**等核心場景。嚴守「輕量、無外部依賴、異常可控」原則，謹慎用於高頻寫入主表。現代架構傾向將複雜邏輯下沉應用層或通過 Binlog 異步處理，觸發器退守「最後一道防線」角色。