MySQL 觸發器（Trigger）使用情境與注意事項

簡介

在資料庫開發與維運過程中，除了 SELECT / INSERT / UPDATE / DELETE 四大基本語句外，觸發器（Trigger） 是一項強大的自動化工具。它允許我們在特定的資料變更事件（INSERT、UPDATE、DELETE）發生前或之後，自動執行一段 SQL 程式碼，從而保證資料完整性、同步其他表格或產生審計紀錄。

對於 初學者 來說，觸發器可能看起來複雜且不易除錯；但對 中階開發者，善用觸發器可以大幅減少程式碼重複、提升系統一致性，甚至在不改動應用層的情況下完成資料治理。本文將從核心概念出發，搭配實務範例，說明觸發器的 使用情境、常見陷阱 以及 最佳實踐，幫助您在 MySQL 專案中安全、有效地運用觸發器。

---

核心概念

1. 觸發器的基本結構

MySQL 的觸發器語法如下：

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
trigger_body;

• trigger_name：觸發器名稱，必須在同一資料庫內唯一。
• BEFORE / AFTER：決定觸發時機，BEFORE 在資料變更前執行，AFTER 在變更完成後執行。
• INSERT / UPDATE / DELETE：觸發的事件類型。
• FOR EACH ROW：表示對每一筆受影響的列都執行一次（行級觸發器），MySQL 目前僅支援行級。
• trigger_body：實際執行的 SQL 陳述式，可包含多條語句（需使用 BEGIN … END 包圍）。

⚠️ 注意：MySQL 不支援 STATEMENT 級觸發器，也不允許在同一表格上同時建立多個相同時間與事件的觸發器（如兩個 BEFORE INSERT）。

2. NEW 與 OLD 關鍵字

在觸發器內，我們可透過 NEW（新值）與 OLD（舊值）來存取資料列的內容：

事件類型	可使用的關鍵字	說明
INSERT	NEW	代表即將被插入的資料列
UPDATE	NEW、OLD	NEW 為更新後的值，OLD 為更新前的值
DELETE	OLD	代表即將被刪除的資料列

3. 限制與注意點

限制項目	說明
每個表格最多只能有 1 個 同時間、同事件的觸發器（如只能有一個 BEFORE INSERT）	若需要多段邏輯，請在同一觸發器內使用 BEGIN … END 包裝多條語句
觸發器內 不允許 呼叫 同一張表的 觸發器（會產生遞迴）	可透過 SIGNAL 產生錯誤或使用 SET @disable_trigger = 1 方式自行控制
觸發器執行期間 不會 自動提交或回滾交易	必須在外層的交易中自行控制 commit/rollback
觸發器內只能使用 SQL，無法直接使用 存儲過程（但可呼叫已存在的存儲過程）	這讓開發者可以把複雜邏輯抽離到 SP 中，保持觸發器簡潔

---

程式碼範例

以下提供 5 個實務常見 的觸發器範例，涵蓋資料驗證、審計、同步、計算欄位與自動編號等情境。每段程式碼均附有說明註解。

範例 1️⃣：自動填入 created_at 與 updated_at

情境：每筆紀錄在插入時記錄建立時間，更新時自動更新最後修改時間。

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 = NOW();
    END IF;
    -- 更新時間一律設為現在
    SET NEW.updated_at = NOW();
END$$

DELIMITER ;

重點：使用 BEFORE INSERT，可以直接修改 NEW 物件的欄位值，無需額外 UPDATE 語句。

範例 2️⃣：防止負數庫存

情境：商品庫存不允許小於 0，若有更新導致負值，直接拋出錯誤。

DELIMITER $$

CREATE TRIGGER trg_stock_non_negative
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '庫存不能為負數';
    END IF;
END$$

DELIMITER ;

說明：SIGNAL 可自訂錯誤訊息，使應用層能捕捉並回報給使用者。

範例 3️⃣：審計表（Audit Log）

情境：所有對 orders 表的 INSERT、UPDATE、DELETE 必須寫入審計表 orders_audit。

DELIMITER $$

CREATE TRIGGER trg_orders_audit_ins
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit
        (order_id, action_type, action_time, user_id, data_snapshot)
    VALUES
        (NEW.id, 'INSERT', NOW(), USER(), ROW_TO_JSON(NEW));
END$$

CREATE TRIGGER trg_orders_audit_upd
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit
        (order_id, action_type, action_time, user_id, data_snapshot)
    VALUES
        (NEW.id, 'UPDATE', NOW(), USER(), ROW_TO_JSON(NEW));
END$$

CREATE TRIGGER trg_orders_audit_del
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_audit
        (order_id, action_type, action_time, user_id, data_snapshot)
    VALUES
        (OLD.id, 'DELETE', NOW(), USER(), ROW_TO_JSON(OLD));
END$$

DELIMITER ;

技巧：利用 ROW_TO_JSON()（MySQL 8.0+）快速取得整列的 JSON 表示，方便日後回溯。

範例 4️⃣：同步資料至另一張表（資料分區或備份）

情境：將 sales_daily 的資料自動同步至 sales_monthly_summary，計算當月累計金額。

DELIMITER $$

CREATE TRIGGER trg_sales_monthly_sync
AFTER INSERT ON sales_daily
FOR EACH ROW
BEGIN
    INSERT INTO sales_monthly_summary
        (store_id, month, total_amount)
    VALUES
        (NEW.store_id, DATE_FORMAT(NEW.sale_date, '%Y-%m'), NEW.amount)
    ON DUPLICATE KEY UPDATE
        total_amount = total_amount + NEW.amount;
END$$

DELIMITER ;

說明：ON DUPLICATE KEY UPDATE 能確保同一月份的統計資料只會累加，不會產生重複列。

範例 5️⃣：自動產生客製化編號（非 AUTO_INCREMENT）

情境：客戶編號需以 CUST-YYYYMMDD-XXXX 形式產生，其中 XXXX 為當日流水號。

DELIMITER $$

CREATE TRIGGER trg_customer_seq
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    DECLARE seq INT;

    -- 取得當天已使用的最大流水號
    SELECT IFNULL(MAX(seq_no), 0) + 1
      INTO seq
      FROM (
            SELECT CAST(SUBSTRING_INDEX(customer_code, '-', -1) AS UNSIGNED) AS seq_no
              FROM customers
             WHERE customer_code LIKE CONCAT('CUST-', DATE_FORMAT(NOW(), '%Y%m%d'), '-%')
           ) AS t;

    SET NEW.customer_code = CONCAT('CUST-',
                                   DATE_FORMAT(NOW(), '%Y%m%d'), '-',
                                   LPAD(seq, 4, '0'));
END$$

DELIMITER ;

重點：透過子查詢取得當天最大序號，再組合成符合規則的編號，避免在應用層自行產生導致競爭條件。

---

常見陷阱與最佳實踐

陷阱	說明	解決或預防方式
遞迴觸發	觸發器內的 INSERT/UPDATE/DELETE 操作同時觸發自己，造成無限迴圈。	避免在同一表上直接寫入；若必須，使用 SET @disable_trigger = 1 做旗標，或改寫為 存儲過程。
效能瓶頸	大量寫入時，每筆資料都會額外執行觸發器，導致延遲。	僅在必要時使用觸發器；對批次作業可暫時 關閉 觸發器（SET SESSION sql_log_bin=0;）或改用 事件排程。
錯誤傳遞	觸發器內的錯誤會使整個語句回滾，且錯誤訊息較不直觀。	使用 SIGNAL 自訂錯誤碼與訊息，並在應用層捕捉；同時在開發環境做好 單元測試。
維護困難	觸發器散落於多個資料庫，難以追蹤變更。	采用 版本控制，將觸發器腳本納入 migration 框架（如 Flyway、Liquibase）。
觸發器限制	同一表只能有一個相同時間與事件的觸發器。	若需要多段邏輯，合併於同一觸發器內，使用 IF … ELSEIF 分支控制。

最佳實踐

1. 保持簡潔：觸發器的核心任務應該是資料驗證、同步或審計，避免寫入大量商業邏輯。
2. 使用明確命名：trg_<table>_<event>_<action> 方式讓人一眼看出觸發器目的。
3. 加入註解：在 BEGIN … END 內使用 -- 註解說明每一步驟，降低日後維護成本。
4. 測試與監控：部署前使用 INSERT/UPDATE/DELETE 測試觸發器行為，並在正式環境加入 慢查詢日誌 與 觸發器執行統計（可透過 performance_schema）。
5. 避免跨資料庫操作：觸發器僅限於同一資料庫內的表，若需跨庫同步，建議使用 事件排程 或 資料複製（Replication）。

---

實際應用場景

場景	觸發器功能	為何適合使用觸發器
電商平台	訂單狀態變更自動更新庫存、建立審計紀錄	庫存必須即時、準確；觸發器保證所有入口（API、後台）一致性。
金融系統	交易前檢查餘額、交易後寫入帳務流水	交易的原子性極為重要，觸發器可在同一交易內完成檢查與記錄。
多租戶 SaaS	自動在每筆資料加入 tenant_id	防止資料洩漏；由觸發器在 INSERT 時自動填入當前租戶編號。
資料倉儲 ETL	每日資料匯入後自動聚合至彙總表	減少額外的批次腳本，確保匯入即時可供報表使用。
合規審計	所有敏感表格的變更寫入審計表、禁止非法欄位變更	法規要求必須保留完整變更紀錄，觸發器提供不可繞過的保護層。

---

總結

MySQL 觸發器是一把雙刃劍：正確使用 能提升資料完整性、減少重複程式碼、實現即時同步與審計；不當使用 則可能造成效能下降、維護困難甚至資料遺失。本文從 概念、實作範例、陷阱與最佳實踐 以及 真實案例 全面說明，期望讀者能在日常開發中：

1. 先思考 是否真的需要觸發器，或可改由應用層或批次作業處理。
2. 設計簡潔、命名清晰 的觸發器，並在開發流程中加入單元測試與版本管理。
3. 監控效能，避免觸發器成為瓶頸，必要時採取暫停或改寫策略。

掌握這些要點後，您就能在 MySQL 專案裡安全、有效地運用觸發器，為系統的資料治理與業務需求提供堅實的後盾。祝開發順利，資料永遠正確！