MySQL 觸發器實戰詳解：BEFORE/AFTER、INSERT/UPDATE/DELETE 完整示範

MySQL 觸發器實戰詳解

觸發器是隨表事件（INSERT/UPDATE/DELETE）自動執行的預定義程式碼，常用於審計日志、數據同步、衍生欄位計算等場景。本文通過完整可運行案例，展示四種觸發器的建立、驗證與查看。

核心限制（必讀）

• 不能使用 CALL 調用動態 SQL，不能直接向客戶端返回結果集
• 不能包含事務控制語句（START TRANSACTION/COMMIT/ROLLBACK）
• 僅在支援事務的引擎（InnoDB）表中才能觸發回滾

實戰環境準備

CREATE DATABASE test1;
USE test1;

CREATE TABLE film_text (
  film_id INT(11), title VARCHAR(20), description VARCHAR(180)
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE film (
  film_id INT(11), title VARCHAR(20), description VARCHAR(180)
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE tri_demo (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  note VARCHAR(50) DEFAULT NULL
) ENGINE=InnoDB CHARSET=utf8;

建立四個觸發器

DELIMITER $$

-- INSERT BEFORE
CREATE TRIGGER ins_film_bef
BEFORE INSERT ON film FOR EACH ROW
BEGIN
  INSERT INTO tri_demo(note) VALUES ('before insert');
END$$

-- INSERT AFTER
CREATE TRIGGER ins_film_aft
AFTER INSERT ON film FOR EACH ROW
BEGIN
  INSERT INTO film_text(title) VALUES ('after insert');
END$$

-- UPDATE BEFORE
CREATE TRIGGER upd_film_bef
BEFORE UPDATE ON film FOR EACH ROW
BEGIN
  INSERT INTO tri_demo(note) VALUES ('before update');
END$$

-- UPDATE AFTER
CREATE TRIGGER upd_film_aft
AFTER UPDATE ON film FOR EACH ROW
BEGIN
  INSERT INTO tri_demo(note) VALUES ('after update');
END$$

DELIMITER ;

驗證執行順序與效果

-- 插入一行
INSERT INTO film VALUES (1, 'film_name', 'film_description');
SELECT * FROM tri_demo;
-- 結果：1 行 'before insert'
-- 注意：AFTER INSERT 向 film_text 寫入，tri_demo 無變化

-- 更新該行
UPDATE film SET title='film_update' WHERE film_id=1;
SELECT * FROM tri_demo;
-- 結果：新增 'before update'、'after update' 兩行
-- 共 3 行：before insert, before update, after update

查看觸發器定義

SHOW TRIGGERS \G;
-- 輸出包含：Trigger 名、Event、Table、Timing、Statement、Definer 等

關鍵觀察點

1. 執行順序：BEFORE → 行操作 → AFTER
2. BEFORE 可修改 NEW 值：SET NEW.col = 'new_val'; 影響最終寫入
3. AFTER 不可修改 NEW：僅能讀取、寫入其他表
4. 觸發器內報錯：會導致原語句回滾（InnoDB）
5. 遞歸觸發：觸發器操作同表再次觸發 → 死循環，需邏輯防護

最佳實踐建議

• 邏輯保持簡單，複雜業務移至應用層或存儲過程
• 避免在觸發器中做耗時 I/O（跨庫、HTTP 請求）
• 命名規範：trg_{表名}_{事件}_{時機}，如 trg_film_ins_bef
• 文檔化：在數據字典記錄每個觸發器用途、影響範圍
• 升級/遷移時務必同步導出觸發器定義