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

MySQL 觸發器入門完整指南：語法、NEW/OLD、增刪改三大場景實戰

編輯：MySQL 綜合教學

MySQL 觸發器入門完整指南：語法、NEW/OLD、增刪改三大場景實戰

觸發器是綁定在表上的事件驅動程式，MySQL 僅支援行級觸發器（強制 FOR EACH ROW）。本文從零開始，涵蓋語法、虛擬表、三大 DML 場景、管理指令與避坑指南。

基礎語法

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- SQL 語句
END$$
DELIMITER ;

DELIMITER $$：臨時修改語句結束符，避免體內 ; 被誤判為結束
BEFORE/AFTER：執行時機
FOR EACH ROW：行級，MySQL 強制

虛擬表 NEW / OLD 可用性對照

事件	NEW	OLD	說明
INSERT	✅ 新行資料	❌	`NEW.col` 可讀寫 (BEFORE) / 唯讀 (AFTER)
UPDATE	✅ 修改後值	✅ 修改前值	可對比 `OLD.col vs NEW.col`
DELETE	❌	✅ 被刪行資料	`OLD.col` 唯讀

實戰場景：商品-訂單庫存同步

建表

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);

1. 下單扣庫存（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 (id=2) num: 26 → 24

2. 取消訂單回庫存（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$$

3. 修改訂單數量同步庫存（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;

-- 查看特定觸發器定義
SHOW CREATE TRIGGER trg_ord_after_insert;

-- 刪除觸發器
DROP TRIGGER trg_ord_after_insert;

查看觸發器輸出範例

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: trg_ord_after_insert
               Event: INSERT
               Table: ord
           Statement: UPDATE goods SET num = num - NEW.much WHERE goods_id = NEW.gid
              Timing: AFTER
             Created: NULL
            sql_mode: NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

避坑指南與最佳實踐

風險	後果	對策
大批量 DML 觸發逐行執行	效能雪崩、鎖爭用	避免高頻大表加觸發器；改用應用層批次/異步處理
觸發器巢狀級聯	死鎖、難以除錯、邏輯失控	嚴禁觸發器修改其他有觸發器的表
AFTER 修改同表	遞歸觸發、報錯	需修改同表用 BEFORE
備份遺漏觸發器	邏輯缺失	`mysqldump --triggers` 預設包含
觸發器內用事務控制	語法錯誤	觸發器體內不可用 START TRANSACTION/COMMIT/ROLLBACK

現代架構定位

觸發器 = 資料庫層 AOP。威力大但副作用強。建議分層：

核心業務邏輯（扣庫存、積分、優惠、狀態機）→ 應用層事務顯式處理

資料完整性最後防線（BEFORE 驗證攔截、格式清洗）→ 觸發器

審計/日誌/衍生同步（AFTER 寫審計表、同步報表表）→ 觸發器或 CDC (Canal/Debezium)

原則：能在應用層解決的，絕不下沉資料庫層。觸發器僅作輔助與防線。