本文 AI 產出，尚未審核

MySQL 觸發器（Trigger）—— BEFORE / AFTER Trigger 完全攻略

簡介

在資料庫應用中，資料完整性、業務規則與自動化處理往往是不可或缺的需求。
MySQL 提供的 Trigger（觸發器）讓我們可以在 INSERT / UPDATE / DELETE 等 DML（資料操作）事件發生前或發生後，自動執行指定的 SQL 程式碼，從而把一些重複且容易遺漏的檢查或維護工作交給資料庫本身負責。

為什麼要使用 Trigger？

  • 避免程式碼重複：同一筆資料的變更可能會被多個應用程式或服務觸發，Trigger 只要寫一次就能全域生效。
  • 保證資料一致性：即使忘記在應用層做檢查，資料庫仍會在寫入前（BEFORE）或寫入後（AFTER）自動校正。
  • 即時審計與統計：可以在資料變更時即時寫入審計表或更新統計資訊，免除批次作業的延遲。

本篇文章將以 BEFORE 與 AFTER 兩種觸發時機為核心，從概念、語法到實務範例，完整說明如何在 MySQL 中運用 Trigger，並提供常見陷阱與最佳實踐，協助你在開發與維運過程中寫出更安全、更易維護的資料庫程式碼。

核心概念

1. Trigger 的基本概念

  • 觸發器（Trigger）：一段與資料表關聯的程式碼，會在特定的 DML 事件（INSERT、UPDATE、DELETE）**發生前（BEFORE）或發生後（AFTER）**自動執行。
  • 事件（Event）：INSERT、UPDATE、DELETE 任一個。
  • 時機（Timing）：BEFORE 或 AFTER。
  • 執行階段：在同一個事務（transaction）內執行，若觸發器內拋出錯誤，整個事務會回滾。

注意：MySQL 只允許 每個表格每種事件只能有一個 BEFORE 與一個 AFTER 觸發器（同一時間點只能有一個）。若需要多段邏輯，請在同一個觸發器內使用多個語句或呼叫儲存程序（Stored Procedure）。

2. BEFORE vs AFTER

時機 執行時機 常見用途 是否可修改 NEW/OLD 欄位
BEFORE 寫入資料之前（INSERT/UPDATE 前） - 資料驗證
- 自動補齊欄位（如自動產生序號、時間戳）
- 防止非法值寫入		 可（僅於 INSERT、UPDATE）
AFTER 寫入資料之後（INSERT/UPDATE/DELETE 後） - 審計紀錄
- 更新統計表
- 觸發外部流程（如呼叫 API）		 不可（只能讀取 NEW/OLD）
  • NEW：在 BEFORE 時可修改（INSERT/UPDATE），在 AFTER 時只能讀取。
  • OLD：在 UPDATE、DELETE 時可讀取（AFTER 亦可），在 INSERT 時不存在。

3. 建立 Trigger 的基本語法

CREATE TRIGGER trigger_name
    { BEFORE | AFTER }
    { INSERT | UPDATE | DELETE }
    ON table_name
    FOR EACH ROW
BEGIN
    -- 這裡寫你的 SQL 程式碼
END;
  • FOR EACH ROW：表示觸發器會對每一筆受影響的資料列執行一次（行級觸發器）。
  • BEGIN … END：允許寫多條語句，若只有單條語句可省略。

小技巧：在開發環境建置觸發器時，建議先使用 DROP TRIGGER IF EXISTS trigger_name; 先刪除舊有的同名觸發器，避免因重複建立而產生錯誤。

4. 程式碼範例

以下提供 5 個實用範例，涵蓋常見的 BEFORE 與 AFTER 使用情境，並加入詳細註解說明。

範例 1：自動填入 created_at 與 updated_at（BEFORE INSERT / BEFORE UPDATE）

-- 先刪除舊的觸發器（如果有的話）
DROP TRIGGER IF EXISTS trg_user_timestamp;
DELIMITER $$

CREATE TRIGGER trg_user_timestamp
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    -- 若未提供 created_at，則自動使用現在時間
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END IF;
    -- 同理，updated_at 也設定為現在時間
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$

CREATE TRIGGER trg_user_timestamp_upd
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    -- 每次更新時都更新 updated_at 欄位
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;

說明：此範例在 插入 時自動填入 created_at，在 更新 時自動更新 updated_at，確保時間戳永遠正確，且不需要在程式碼裡每次手動設定。

範例 2：限制庫存不可為負（BEFORE UPDATE）

DROP TRIGGER IF EXISTS trg_product_stock_check;
DELIMITER $$

CREATE TRIGGER trg_product_stock_check
    BEFORE UPDATE ON products
    FOR EACH ROW
BEGIN
    -- 若新庫存量小於 0，則拋出錯誤，回滾交易
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '庫存量不能小於 0';
    END IF;
END$$
DELIMITER ;

說明：當業務邏輯需要保證庫存永遠為非負值時，使用 BEFORE UPDATE 可以在寫入前直接阻止非法資料，避免後續產生錯誤的訂單或報表。

範例 3：自動產生客戶代號（BEFORE INSERT）

DROP TRIGGER IF EXISTS trg_customer_code;
DELIMITER $$

CREATE TRIGGER trg_customer_code
    BEFORE INSERT ON customers
    FOR EACH ROW
BEGIN
    -- 若未提供客戶代號，使用自訂規則產生 (e.g., CUST_YYYYMMDD_自增序號)
    IF NEW.customer_code IS NULL THEN
        SET NEW.customer_code = CONCAT('CUST_', DATE_FORMAT(CURDATE(), '%Y%m%d'), '_',
                                      LPAD((SELECT IFNULL(MAX(id),0)+1 FROM customers), 4, '0'));
    END IF;
END$$
DELIMITER ;

說明：此範例示範如何在 插入新客戶 時，根據當天日期與自增序號自動產生唯一的 customer_code，減少前端產生代號的負擔。

範例 4：寫入審計表（AFTER INSERT / AFTER UPDATE / AFTER DELETE）

DROP TABLE IF EXISTS audit_log;
CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tbl_name VARCHAR(64),
    operation ENUM('INSERT','UPDATE','DELETE'),
    pk_value VARCHAR(255),
    old_data TEXT,
    new_data TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(64)
);

DROP TRIGGER IF EXISTS trg_audit_all;
DELIMITER $$

CREATE TRIGGER trg_audit_all
    AFTER INSERT ON orders
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (tbl_name, operation, pk_value, old_data, new_data, changed_by)
    VALUES ('orders', 'INSERT', NEW.id, NULL,
            JSON_OBJECT('customer_id', NEW.customer_id, 'total', NEW.total), USER());
END$$

CREATE TRIGGER trg_audit_upd
    AFTER UPDATE ON orders
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (tbl_name, operation, pk_value, old_data, new_data, changed_by)
    VALUES ('orders', 'UPDATE', NEW.id,
            JSON_OBJECT('customer_id', OLD.customer_id, 'total', OLD.total),
            JSON_OBJECT('customer_id', NEW.customer_id, 'total', NEW.total), USER());
END$$

CREATE TRIGGER trg_audit_del
    AFTER DELETE ON orders
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (tbl_name, operation, pk_value, old_data, new_data, changed_by)
    VALUES ('orders', 'DELETE', OLD.id,
            JSON_OBJECT('customer_id', OLD.customer_id, 'total', OLD.total), NULL, USER());
END$$
DELIMITER ;

說明：此範例利用 AFTER 觸發器將每一次資料變更寫入 audit_log，使用 JSON_OBJECT 儲存舊值與新值，方便日後檢索與分析。USER() 取得執行操作的資料庫使用者。

範例 5：同步統計表（AFTER INSERT / AFTER DELETE）

DROP TABLE IF EXISTS product_sales_summary;
CREATE TABLE product_sales_summary (
    product_id BIGINT PRIMARY KEY,
    total_qty BIGINT NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00
);

DROP TRIGGER IF EXISTS trg_sales_summary_ins;
DELIMITER $$

CREATE TRIGGER trg_sales_summary_ins
    AFTER INSERT ON order_items
    FOR EACH ROW
BEGIN
    INSERT INTO product_sales_summary (product_id, total_qty, total_amount)
        VALUES (NEW.product_id, NEW.quantity, NEW.quantity * NEW.unit_price)
    ON DUPLICATE KEY UPDATE
        total_qty = total_qty + NEW.quantity,
        total_amount = total_amount + NEW.quantity * NEW.unit_price;
END$$

DROP TRIGGER IF EXISTS trg_sales_summary_del;
CREATE TRIGGER trg_sales_summary_del
    AFTER DELETE ON order_items
    FOR EACH ROW
BEGIN
    UPDATE product_sales_summary
        SET total_qty = total_qty - OLD.quantity,
            total_amount = total_amount - OLD.quantity * OLD.unit_price
        WHERE product_id = OLD.product_id;
END$$
DELIMITER ;

說明：當 order_items 新增或刪除時，自動更新 product_sales_summary 統計表，避免每日跑批次彙總，提高即時報表的準確度。

常見陷阱與最佳實踐

陷阱 說明 解決方案或最佳做法
觸發器遞迴 觸發器內部的 INSERT/UPDATE/DELETE 會再次觸發相同或其他觸發器，若未加限制會造成無限遞迴。 - 使用 SET @disable_trigger = 1; 之類的 session 變數作為開關；
- 在觸發器內部加條件 IF @disable_trigger IS NULL THEN … END IF;
性能衝擊 每筆資料的變更都會額外執行觸發器程式碼，若程式碼過於複雜會拖慢 DML 操作。 - 只在必要時使用，將邏輯保持簡潔；
- 若需大量計算，考慮改寫為 事件排程（Event Scheduler） 或 應用層批次。
不可見的錯誤 觸發器內拋出的錯誤會導致整個事務回滾，開發者有時不易察覺。 - 使用 SIGNAL 明確拋出錯誤訊息；
- 在測試環境加入 SHOW WARNINGS; 觀察觸發器的警告。
欄位限制 BEFORE 觸發器只能在 INSERT、UPDATE 時使用 NEW，而 AFTER 只能讀取，不能修改。 - 在需要修改值的情況下，務必使用 BEFORE；
- 若要在 AFTER 做額外寫入，請使用 INSERT/UPDATE 到其他表。
同表多觸發器衝突 MySQL 只允許同一表同一事件同一時機一個觸發器，若需要多段邏輯只能合併。 - 集中管理：將相關邏輯寫在同一觸發器內，或呼叫共用的 Stored Procedure。
資料庫備份/還原 觸發器屬於結構物件，需要在備份腳本中包含 SHOW TRIGGERS 或 mysqldump --triggers。 - 使用 mysqldump 時加上 --routines --triggers 參數，確保備份完整。

最佳實踐小結

  1. 先設計再實作：先在需求文件或 ER 圖中標註哪些欄位需要自動維護，避免事後補救。
  2. 保持簡潔：觸發器內的程式碼最好不超過 5–10 行，若邏輯較複雜，考慮呼叫 Stored Procedure。
  3. 加上註解：在觸發器上方寫下用途、建立者、版本與注意事項，方便日後維護。
  4. 測試覆蓋：使用單元測試或腳本驗證所有 DML 路徑（INSERT、UPDATE、DELETE）是否正確觸發。
  5. 監控效能：在生產環境啟用慢查詢日誌（slow query log），觀察觸發器是否成為瓶頸。

實際應用場景

場景 需要的 Trigger 為什麼適合使用 Trigger
訂單系統：自動產生訂單編號、更新庫存、寫入審計 BEFORE INSERT（產生編號）
AFTER INSERT（減庫存）
AFTER INSERT（審計）		 保證編號唯一、即時庫存同步、完整審計紀錄不依賴應用程式。
會員管理：密碼加鹽、登入失敗次數累計、帳號凍結 BEFORE INSERT/UPDATE（密碼雜湊）
AFTER UPDATE（失敗次數）		 密碼雜湊在 DB 層統一執行，避免忘記加密；失敗次數統計即時更新。
資料倉儲：每日銷售統計即時累加 AFTER INSERT/DELETE（order_items） 免除每日離線批次，直接在交易發生時即時更新統計表。
合規審計：所有敏感欄位變更必須留下痕跡 AFTER UPDATE/DELETE（客戶資料） 法規要求變更紀錄，Trigger 可保證無論哪個系統寫入都會留下痕跡。
多語系商品描述：自動同步主表與翻譯表 AFTER INSERT/UPDATE（products） 新增或更新商品時，同步寫入或更新對應的語系描述表，保持資料一致。

總結

  • BEFORE 觸發器適合在資料寫入前驗證、修改或補齊欄位；AFTER 觸發器則適合在資料已寫入後審計、統計或同步其他表。
  • 正確使用 NEW、OLD、SIGNAL 與 ON DUPLICATE KEY UPDATE 等技巧，可讓觸發器既安全又高效。
  • 避免遞迴、過度複雜的邏輯與過度依賴觸發器，保持 簡潔、可測試、易維護 的設計原則。
  • 透過本文提供的 實務範例，你可以快速在自己的 MySQL 專案中加入自動化的資料完整性與審計機制，提升系統可靠性與開發效率。

最後提醒：在正式上線前，務必在測試環境完整驗證觸發器的行為與效能，並將觸發器的建立腳本納入版本控制與部署流程，才能確保每一次資料庫變更都如預期般安全、可靠。祝你寫出乾淨、穩定的 MySQL Trigger！