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

MySQL 觸發器詳解：FOR EACH ROW 行級觸發機制與實戰驗證

編輯：MySQL 綜合教學

MySQL 觸發器詳解：FOR EACH ROW 行級觸發機制與實戰驗證

MySQL 僅支援行級觸發器，強制要求 FOR EACH ROW 子句。每影響一行資料，觸發器體就執行一次。這與 Oracle 支援語句級觸發器（可省略 FOR EACH ROW）形成鮮明對比。

語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- 觸發器邏輯
  SQL 语句;
END;

核心特性

行級語義：UPDATE t SET x=1 WHERE id IN (1,2,3) 影響 3 行 → 觸發器執行 3 次
時機：BEFORE（寫入前，可修改 NEW 值）vs AFTER（寫入後，不可修改）
虛擬表：
- NEW：INSERT/UPDATE 的新行資料（NEW.col）
- OLD：UPDATE/DELETE 的舊行資料（OLD.col）
事件限制：觸發器內不能呼叫 CALL、START TRANSACTION、COMMIT、ROLLBACK（除非自主事務）

實戰驗證：訂單-庫存同步場景

建表

CREATE TABLE goods (
  goods_id INT PRIMARY KEY,
  name VARCHAR(20),
  num SMALLINT
) ENGINE=InnoDB;

CREATE TABLE `ord` (
  oid INT PRIMARY KEY,
  gid INT,
  much INT
) ENGINE=InnoDB;

INSERT INTO goods VALUES (1,'cat',26),(2,'dog',26),(3,'pig',26);

觸發器：下單扣庫存（AFTER INSERT）

DELIMITER $$
CREATE TRIGGER trg_ord_after_insert
AFTER INSERT ON `ord`
FOR EACH ROW
BEGIN
  UPDATE goods SET num = num - NEW.much WHERE goods_id = NEW.gid;
END$$
DELIMITER ;

驗證

INSERT INTO `ord` VALUES (1, 2, 2);
SELECT * FROM goods;
-- 結果：dog (goods_id=2) num 從 26 變 24

觸發器：取消訂單回庫存（AFTER DELETE）

CREATE TRIGGER trg_ord_after_delete
AFTER DELETE ON `ord`
FOR EACH ROW
BEGIN
  UPDATE goods SET num = num + OLD.much WHERE goods_id = OLD.gid;
END$$

觸發器：修改訂單數量同步庫存（AFTER UPDATE）

CREATE TRIGGER trg_ord_after_update
AFTER UPDATE ON `ord`
FOR EACH ROW
BEGIN
  UPDATE goods SET num = num + OLD.much - NEW.much WHERE goods_id = NEW.gid;
END$$

管理指令

-- 查看所有觸發器
SHOW TRIGGERS;

-- 刪除觸發器
DROP TRIGGER trg_ord_after_insert;

效能與避坑指南

風險	影響	對策
大批量 DML 觸發器逐行執行	效能指數級下降，鎖爭用劇增	避免在高頻大表加觸發器；改用應用層批次處理或異步佇列
觸發器內再觸發其他表觸發器	級聯觸發、死鎖、難以除錯	嚴禁觸發器巢狀超過 1 層；業務邏輯下沉應用層
觸發器修改 NEW 值（BEFORE）	可實現資料清洗/預設值填充	僅在 BEFORE 觸發器中使用 `SET NEW.col = value;`
備份/遷移遺漏觸發器	業務邏輯缺失	`mysqldump --triggers` 預設包含；遷移腳本驗證 `SHOW TRIGGERS`