觸發器執行時機決定了能否修改即將寫入的資料、能否感知寫入結果。BEFORE：寫入前，可攔截修改 NEW 值；AFTER：寫入後，不可修改，可感知生成的自增 ID。本文透過「訂單-庫存」場景深度對比。
| 維度 | BEFORE | AFTER |
|---|---|---|
| 執行時機 | 儲存引擎寫入資料頁前 | 儲存引擎寫入資料頁後 |
| 可修改 NEW | ✅ SET NEW.col = val; | ❌ 唯讀 |
| 可讀取 OLD | UPDATE/DELETE 可用 | UPDATE/DELETE 可用 |
| 可感知自增 ID | ❌ 尚未生成 | ✅ NEW.id 已有值 |
| 事務回滾影響 | 若事務回滾，BEFORE 修改也回滾 | 若事務回滾，AFTER 操作也回滾 |
| 典型用途 | 資料清洗、預設值填充、驗證攔截、防爆庫 | 審計日誌、衍生表同步、發送通知、異步佇列寫入 |
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
name VARCHAR(20),
num SMALLINT -- 庫存
) ENGINE=InnoDB;
CREATE TABLE `ord` (
oid INT PRIMARY KEY AUTO_INCREMENT,
gid INT,
much INT -- 下單數量
) ENGINE=InnoDB;
INSERT INTO goods VALUES (1,'cat',26),(2,'dog',26),(3,'pig',26);CREATE TRIGGER trg_after_insert
AFTER INSERT ON `ord`
FOR EACH ROW
BEGIN
UPDATE goods SET num = num - NEW.much WHERE goods_id = NEW.gid;
END$$問題：訂單已寫入 → 觸發器執行扣減 → 庫存不足時無法阻止訂單建立 → num 變負數（爆庫）。
INSERT INTO `ord` (gid, much) VALUES (2, 30); -- 庫存只有 26
-- 訂單建立成功，goods.num 變為 -4 ❌CREATE TRIGGER trg_before_insert
BEFORE INSERT ON `ord`
FOR EACH ROW
BEGIN
DECLARE stock INT;
SELECT num INTO stock FROM goods WHERE goods_id = NEW.gid;
IF NEW.much > stock THEN
-- 策略 1：拋出錯誤阻止下單
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
-- 策略 2：自動修正為最大可買數量（演示用）
-- SET NEW.much = stock;
END IF;
-- 扣減庫存（同一事務內，原子性保證）
UPDATE goods SET num = num - NEW.much WHERE goods_id = NEW.gid;
END$$優勢：
NEW.much 為合法值CREATE TRIGGER trg_audit_after_insert
AFTER INSERT ON `ord`
FOR EACH ROW
BEGIN
INSERT INTO order_audit (order_id, action, qty, created_at)
VALUES (NEW.oid, 'CREATE', NEW.much, NOW());
END$$需在 AFTER 才能獲取自增 NEW.oid。
| 操作 | BEFORE 適用 | AFTER 適用 |
|---|---|---|
| UPDATE | 修正 NEW 值、驗證變更合法性、記錄變更前值 (OLD) | 同步衍生表、寫審計日誌 (含 NEW/OLD 對比) |
| DELETE | 檢查刪除條件、軟刪除標記 (UPDATE 代替 DELETE) | 清理關聯資料、寫刪除審計日誌 |
-- BEFORE 觸發器中阻止非法寫入
IF NEW.email NOT LIKE '%@%' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '郵箱格式無效',
MYSQL_ERRNO = 1001;
END IF;SQLSTATE '45000'：通用使用者定義錯誤SQLException 解析錯誤碼/訊息mysqldump --triggers 預設包含選擇時機的黃金法則：需要修改寫入值/阻止寫入 → BEFORE；需要感知寫入結果(自增ID)/不修改源表 → AFTER。現代架構傾向將業務邏輯上移應用層，觸發器退守為「資料完整性最後防線」與「審計日誌自動化」。
