您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 觸發器完整指南：語法、NEW/OLD 行引用、BEFORE/AFTER 差異與防惡意訂單實戰

編輯：MySQL 綜合教學

觸發器概念

觸發器是**與表事件綁定**的特殊存儲程序，當表發生 INSERT/UPDATE/DELETE 時自動執行定義的 SQL，實現**行級業務邏輯自動化**（如同步冗餘字段、審計日誌、約束檢查）。

創建語法

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name
  FOR EACH ROW
BEGIN
  -- SQL 語句 (可多條，用分號分隔)
END;

刪除與查看

DROP TRIGGER [IF EXISTS] trigger_name;
SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name\G

NEW / OLD 行引用規則

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

BEFORE vs AFTER 核心差異

事件	NEW	OLD	說明
INSERT	✓ 新行值	✗	無舊行
UPDATE	✓ 修改後值	✓ 修改前值	雙向可用
DELETE	✗	✓ 被刪行值	無新行

維度	BEFORE	AFTER
執行順序	觸發器先執行，然後執行 DML	DML 先執行，然後觸發器
可修改 NEW	是（可攔截/修改寫入值）	否（已寫入）
可讀 OLD	是	是
典型用途	數據校驗、自動填充、值修正、阻止操作	審計日誌、同步衍生表、異步通知
事務影響	同一事務內，觸發器失敗 → DML 回滾	同一事務內，觸發器失敗 → DML 回滾

實戰案例 1：下單自動扣減庫存（AFTER INSERT）

CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON `order`
FOR EACH ROW
BEGIN
  UPDATE goods 
  SET stock = stock - NEW.num 
  WHERE id = NEW.goods_id;
END;

實戰案例 2：防惡意大量訂單（BEFORE INSERT 修正值）

CREATE TRIGGER trg_order_before_insert
BEFORE INSERT ON `order`
FOR EACH ROW
BEGIN
  IF NEW.num > 5 THEN
    SET NEW.num = 5;  -- 強制截單上限
  END IF;
END;

實戰案例 3：刪除訂單歸還庫存（AFTER DELETE）

CREATE TRIGGER trg_order_after_delete
AFTER DELETE ON `order`
FOR EACH ROW
BEGIN
  UPDATE goods 
  SET stock = stock + OLD.num 
  WHERE id = OLD.goods_id;
END;

實戰案例 4：更新審計日誌（BEFORE UPDATE 記錄舊值）

CREATE TRIGGER trg_user_before_update
BEFORE UPDATE ON `user`
FOR EACH ROW
BEGIN
  INSERT INTO user_audit (user_id, field, old_val, new_val, mod_time)
  VALUES (OLD.id, 'status', OLD.status, NEW.status, NOW());
END;

避坑指南

觸發器內不可顯式 COMMIT/ROLLBACK（隸屬觸發 DML 的事務）。
避免觸發器鏈式調用死循環：A 表觸發器更新 B 表，B 表觸發器又更新 A 表。
性能影響：大批量導入時觸發器逐行執行極慢，**導入前禁用觸發器**（需 SUPER 權限）：SET GLOBAL log_bin_trust_function_creators=1 等變量無直接開關，建議導入用 LOAD DATA 繞過觸發器（5.7+ 不觸發）。
主從複製：觸發器定義隨表結構同步，**從庫同樣執行**，注意 DEFINER 主機一致性。
異常處理：觸發器內拋出異常會導致 DML 失敗，可用 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 捕獲並記錄而不中斷（視業務而定）。