MySQL 觸發器 (Trigger) – INSERT / UPDATE / DELETE 完全指南

簡介

在資料庫應用中，資料一致性與業務規則往往需要在資料變更時自動執行特定流程。
傳統上，我們會在應用程式層面寫程式碼去檢查或更新相關表格，然而這樣會產生以下問題：

程式碼散落：相同的驗證或補償邏輯可能被多個服務重複實作，維護成本高。
資料不一致：若某個入口忘記呼叫相應的檢查，資料就可能破壞約束條件。

MySQL 的 觸發器 (Trigger) 為我們提供了一個「資料變更即時執行」的機制，讓 INSERT、UPDATE、DELETE 三種 DML 操作都能自動觸發自訂程式碼。透過觸發器，我們可以：

在資料寫入前/後自動 驗證、補齊或記錄。
把 跨表的同步、稽核、計算欄位 等業務規則集中管理。
減少應用層的重複程式碼，提高系統的 可維護性 與 可靠性。

本篇文章將從概念、語法、實作範例、常見陷阱與最佳實踐，帶你一步步掌握 MySQL 觸發器的使用，適合 初學者 也能讓 中級開發者 獲得實務上的深度了解。

核心概念

1. 觸發器的基本結構

MySQL 觸發器的語法大致如下（以下示範使用 CREATE TRIGGER）：

CREATE TRIGGER trigger_name                -- 觸發器名稱 (全域唯一)
    BEFORE|AFTER                           -- 觸發時機：執行前或執行後
    INSERT|UPDATE|DELETE                   -- 觸發事件
    ON table_name                           -- 目標資料表
    FOR EACH ROW                           -- 每筆受影響的列都會執行一次
BEGIN
    -- 觸發器內部的 SQL 程式碼
END;

BEFORE 觸發器在資料變更前執行，常用於 驗證或修改即將寫入的資料 (NEW 變數)。
AFTER 觸發器在資料變更後執行，適合 寫入稽核紀錄、更新相關表格 (OLD 變數可取得舊值)。
每個觸發器只能針對 單一事件（INSERT、UPDATE、DELETE）且 只能有一個 BEFORE/AFTER，若需要同時處理多個事件，需要建立多個觸發器。

2. NEW 與 OLD 變數

在觸發器內部，MySQL 提供兩個特殊的行級變數：

變數	何時可用	代表意義
`NEW.column_name`	INSERT、UPDATE（BEFORE/AFTER）	即將寫入或更新後的值
`OLD.column_name`	UPDATE、DELETE（BEFORE/AFTER）	變更前或被刪除前的舊值

注意：在 AFTER INSERT 中只能讀取 NEW，而 BEFORE UPDATE 中同時可以讀寫 NEW（修改即將寫入的值）與讀取 OLD。

3. 觸發器的限制

限制項目	說明
同一張表同一事件只能有一個 BEFORE 或 AFTER 觸發器	若需要多段邏輯，請在同一觸發器內寫多個語句或利用儲存過程。
觸發器內部不允許使用 COMMIT、ROLLBACK	觸發器本身屬於同一個事務的一部分，提交或回滾由外層控制。
觸發器不能直接遞迴呼叫本身（MySQL 5.7+ 允許遞迴，但必須在 `log_bin_trust_function_creators` 設定為 1）	建議避免遞迴，以免產生不可預期的無限迴圈。
觸發器內部不允許使用動態 SQL（如 `PREPARE`）	若需要動態行為，請改寫為儲存過程或在應用層完成。

程式碼範例

下面提供 5 個實務上常見 的觸發器範例，涵蓋 INSERT、UPDATE、DELETE 的 BEFORE/AFTER 用法，並加上詳細註解說明。

範例 1：INSERT BEFORE – 自動填入 `created_at` 與 `created_by`

假設有一個 orders 訂單表，我們希望每筆新訂單在插入時自動帶入建立時間與建立者（由 SESSION 變數傳入）。

DELIMITER $$

CREATE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- 若未提供 created_at，使用目前時間
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;

    -- 由應用程式設定的變數 @current_user，若未設定則設為 'system'
    SET NEW.created_by = COALESCE(@current_user, 'system');
END$$

DELIMITER ;

說明：BEFORE INSERT 允許我們直接修改 NEW 行的欄位值，避免在程式端每次都寫 INSERT ... VALUES (NOW(), @user, ...)。

範例 2：UPDATE BEFORE – 防止負的庫存值

在 products 表中，stock 欄位代表庫存量。若更新導致庫存變成負數，我們要自動把它改為 0，並記錄警告訊息。

DELIMITER $$

CREATE TRIGGER trg_products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock < 0 THEN
        SET NEW.stock = 0;
        -- 使用 MySQL 內建的訊息功能寫入錯誤訊息 (僅示範，實務上可寫入 audit 表)
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不能為負數，已自動調整為 0';
    END IF;
END$$

DELIMITER ;

說明：SIGNAL 可以在觸發器內拋出自訂錯誤，讓呼叫端立即得知異常。此例同時把非法值修正為 0，避免資料錯亂。

範例 3：AFTER INSERT – 寫入稽核表

每當有新會員註冊，我們想把相關資訊寫入 audit_log 表作為稽核。

DELIMITER $$

CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        operation,
        record_id,
        changed_at,
        changed_by,
        description
    ) VALUES (
        'users',
        'INSERT',
        NEW.id,
        NOW(),
        COALESCE(@current_user, 'system'),
        CONCAT('新會員註冊，email=', NEW.email)
    );
END$$

DELIMITER ;

說明：AFTER INSERT 中只能讀取 NEW，適合把變更紀錄寫入另一張表，保留完整的稽核歷史。

範例 4：AFTER UPDATE – 同步維護統計表

假設有一張 sales_summary 統計表，記錄每日的銷售總額。每次 sales 表的 amount 欄位被更新，都要同步調整對應日期的統計。

DELIMITER $$

CREATE TRIGGER trg_sales_after_update
AFTER UPDATE ON sales
FOR EACH ROW
BEGIN
    -- 只在金額變更時才更新統計
    IF NEW.amount <> OLD.amount THEN
        UPDATE sales_summary
        SET total_amount = total_amount
            - OLD.amount   -- 減去舊金額
            + NEW.amount   -- 加上新金額
        WHERE sale_date = DATE(NEW.sale_time);
    END IF;
END$$

DELIMITER ;

說明：利用 OLD 與 NEW 的差值直接調整統計，避免重新計算全表，提高效能。

範例 5：AFTER DELETE – 刪除相關子表資料

當 customers 表的客戶被刪除時，我們希望自動把該客戶的所有訂單 (orders) 也一起刪除，避免孤兒資料。

DELIMITER $$

CREATE TRIGGER trg_customers_after_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE customer_id = OLD.id;
END$$

DELIMITER ;

說明：雖然可以使用外鍵 ON DELETE CASCADE 完成相同功能，但在某些情況（如跨資料庫、需要額外邏輯）時，觸發器提供更彈性的處理方式。

常見陷阱與最佳實踐

陷阱	風險	解決或最佳做法
觸發器過度堆疊	多個觸發器相互呼叫，導致效能下降或無限迴圈。	只建立必要的觸發器，將複雜邏輯抽成儲存過程，在觸發器內呼叫。
未考慮批次操作 (`INSERT ... SELECT`)	觸發器會對每一筆資料執行一次，若批次很大會造成嚴重性能問題。	在大量匯入時暫時停用觸發器 (`SET foreign_key_checks = 0;` & `DROP TRIGGER` 或 `ALTER TABLE ... DISABLE TRIGGER` 在 MariaDB)；或改用事件 (Event) 事後處理。
使用外部變數 (`@var`) 不一致	變數在不同連線間不共享，可能導致 `NULL` 或錯誤值。	使用 SESSION 變數並在每次連線前明確設置，或將必要資訊寫入 temporary table 再由觸發器讀取。
忘記處理 `NULL`	`IF NEW.col = ''` 會在 `NULL` 時失效，導致錯誤邏輯。	使用 `COALESCE()` 或 `IFNULL()` 先將 `NULL` 轉為預設值再比較。
在觸發器內執行大量 I/O (如大量 INSERT/UPDATE)	每筆資料都會觸發，導致鎖爭用與事務延長。	把大量寫入改為暫存表 + 批次處理，或使用 AFTER 觸發器寫入 queue 再由背景工作者處理。

最佳實踐小結

保持簡潔：觸發器應只做「資料層面的」驗證、補齊或稽核，不宜放太多業務邏輯。
使用 SIGNAL 明確回傳錯誤，讓應用層能即時捕獲。
命名規範：建議使用 trg_<table>_<timing>_<event>，如 trg_orders_before_insert，方便維護。
測試覆蓋：在開發環境使用 EXPLAIN、SHOW TRIGGERS 檢查觸發器是否正確建立，並撰寫 單元測試（可利用 mysql-test-run.pl）確保邏輯不會因為資料變化而失效。
文件化：在資料庫說明文件或版本控制中紀錄每支觸發器的目的、參與欄位與副作用，避免日後遺忘。

實際應用場景

場景	觸發器類型	為何適合使用觸發器
多系統資料同步（例如 ERP 與 CRM 共享客戶資料）	`AFTER UPDATE` / `AFTER INSERT`	當主表更新時自動寫入同步表或發送訊息，確保即時一致性。
稽核與合規（金融、醫療的操作紀錄）	`AFTER INSERT/UPDATE/DELETE`	自動把每筆變更寫入 `audit_log`，不需要在每個應用程式都寫稽核程式。
自動產生編號或代碼（訂單編號、發票號碼）	`BEFORE INSERT`	使用 `NEW.column = CONCAT('ORD', DATE_FORMAT(NOW(),'%Y%m%d'), LPAD(...))`，保證唯一且不必在程式端自行產生。
庫存或金額校正（防止負數、超額）	`BEFORE UPDATE`	在資料寫入前即時檢查，若不符合條件直接阻止或修正，降低資料錯誤風險。
刪除級聯（無法使用外鍵或跨資料庫）	`AFTER DELETE`	手動刪除相關子表或觸發外部 API，實現更彈性的清理機制。

總結

MySQL 的 INSERT / UPDATE / DELETE 觸發器 為資料庫層提供了強大的即時自動化能力。透過 BEFORE 與 AFTER 兩種時機，我們可以：

在寫入前 完成驗證、欄位填補或錯誤阻止。
在寫入後 寫入稽核、同步統計、或執行跨表操作。

然而，觸發器也不是萬能的解藥：過度使用、未考慮批次效能或缺乏測試都可能成為系統瓶頸。遵循 簡潔、命名一致、文件化 的最佳實踐，並在需要時結合 儲存過程、事件排程 或 應用層邏輯，才能發揮觸發器的最大價值。

關鍵 takeaway：
使用觸發器讓資料完整性與業務規則在資料庫層得到保障，同時減少程式碼重複，提升系統的可維護性與安全性。

祝你在 MySQL 觸發器的旅程中寫出乾淨、可靠的資料庫程式碼！ 🚀