MySQL 觸發器入門實戰：語法、BEFORE/AFTER 行級觸發、NEW/OLD 伪表、執行順序與失敗回滾機制

觸發器基礎語法

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

核心：NEW / OLD 伪表 (行級上下文)

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

實戰案例 1：同步備份表 (AFTER INSERT / DELETE)

-- 建表
CREATE TABLE tab1 (id VARCHAR(11) PRIMARY KEY);
CREATE TABLE tab2 (id VARCHAR(11) PRIMARY KEY);

-- AFTER INSERT: 同步新增
CREATE TRIGGER t_after_insert_on_tab1
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
    INSERT INTO tab2 (id) VALUES (NEW.id);
END;

-- AFTER DELETE: 同步刪除
CREATE TRIGGER t_after_delete_on_tab1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
    DELETE FROM tab2 WHERE id = OLD.id;
END;

-- 測試
INSERT INTO tab1 VALUES ('0001');
SELECT * FROM tab1;  -- 0001
SELECT * FROM tab2;  -- 0001

DELETE FROM tab1 WHERE id = '0001';
SELECT * FROM tab1;  -- 空
SELECT * FROM tab2;  -- 空

實戰案例 2：數據校驗與自動修正 (BEFORE INSERT/UPDATE)

CREATE TRIGGER t_before_insert_on_user
BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
    -- 1. 手機號格式校驗
    IF NEW.phone NOT REGEXP '^1[3-9][0-9]{9}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '手機號格式錯誤';
    END IF;
    
    -- 2. 自動生成創建時間
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
    
    -- 3. 敏感字過濾 (示例)
    SET NEW.nickname = REPLACE(NEW.nickname, '敏感詞', '***');
END;

關鍵點：SIGNAL SQLSTATE '45000' 主動拋出異常，**導致觸發器所在語句回滾** (INSERT/UPDATE 不執行)。

觸發器執行順序與事務交互 (核心難點)

1. BEFORE 觸發器 → 語句執行 → AFTER 觸發器**，全部在**同一事務**中。
2. BEFORE 觸發器失敗 (SIGNAL/報錯) → 語句不執行 → AFTER 觸發器不執行 → 事務回滾**。
3. 語句執行失敗 (如唯一鍵衝突) → AFTER 觸發器不執行 → 事務回滾** (BEFORE 已執行完成)。
4. AFTER 觸發器失敗 → 語句已執行完成，但事務回滾** (語句效果撤銷)。

實驗驗證：BEFORE 失敗導致 SQL 回滾

-- 建立 BEFORE UPDATE 觸發器，故意報錯
CREATE TRIGGER t_bu_planinfo
BEFORE UPDATE ON planinfo
FOR EACH ROW
BEGIN
    INSERT INTO not_exist_table VALUES (NEW.planid);  -- 表不存在，報錯
END;

-- 執行 UPDATE
UPDATE planinfo SET showprob = 200 WHERE planid = 1;
-- ERROR 1146 (42S02): Table 'not_exist_table' doesn't exist

-- 檢查數據
SELECT showprob FROM planinfo WHERE planid = 1;  -- 仍為舊值，UPDATE 已回滾

結論：BEFORE 觸發器是**守門員**，任何錯誤直接攔截主操作，保證原子性。

權限與管理

• 創建需 TRIGGER 權限 (5.1.6+ 獨立權限，早期需 SUPER)。
• 查看：SHOW TRIGGERS; / SELECT * FROM information_schema.TRIGGERS;。
• 刪除：DROP TRIGGER [IF EXISTS] [schema.]trigger_name;。
• 觸發器定義存儲於 .TRG 文件 (表級) 和 information_schema。

避坑指南與最佳實踐

1. 業務邏輯優先放應用層**：觸發器隱蔽、難調試、擴展性差、遷移麻煩。僅用於**跨應用/舊系統無法修改**的數據完整性守門、審計日誌、異步同步觸發。
2. 避免觸發器鏈/遞歸**：觸發器內操作同表再觸發 → 死循環/棧溢出。用會話變量標記 SET @in_trigger = 1; 判斷跳出。
3. 大批量操作性能殺手**：LOAD DATA、DELETE FROM table (無 WHERE) 會逐行觸發，**極慢**。大批量操作前 DROP TRIGGER，事後重建。
4. 備份/遷移必須包含觸發器**：mysqldump --triggers (默認開啟)。
5. 複製場景**：從庫默認**不執行**主庫觸發器 (避免雙重執行)，需 log_bin_trust_function_creators=1 等配置，或主庫不建觸發器、從庫單獨建。