MySQL 觸發器完整指南：建立、監控、審計日誌與刪除實戰範例

MySQL 5.x 觸發器：從入門到實戰審計應用

觸發器是針對特定表事件（INSERT/UPDATE/DELETE）自動執行的程式碼塊，適用於審計日誌、衍生資料維護、跨表同步、資料完約束等場景。本文以 MySQL 5.x 為基礎，詳細說明建立語法、狀態檢視、實戰審計案例與清理操作。

核心概念與語法

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
trigger_body

• trigger_time：BEFORE（寫入前，可修改 NEW 值）| AFTER（寫入後，不可修改）
• trigger_event：INSERT | UPDATE | DELETE
• NEW / OLD：NEW.col 為新值（INSERT/UPDATE），OLD.col 為舊值（UPDATE/DELETE）
• FOR EACH ROW：逐列觸發（MySQL 僅支援行級觸發器）

範例一：自動統計字符總長度

-- 基礎表
CREATE TABLE `data` (`name` VARCHAR(255));
CREATE TABLE `chars` (`total_len` INT DEFAULT 0);
INSERT INTO `chars` VALUES (0);

-- 觸發器：INSERT 後累加字符長度
CREATE TRIGGER `trg_data_ins_len` 
AFTER INSERT ON `data`
FOR EACH ROW
UPDATE `chars` SET `total_len` = `total_len` + CHAR_LENGTH(NEW.`name`);

-- 測試
INSERT INTO `data` (`name`) VALUES ('Sue'), ('Jane');
SELECT * FROM `chars`;  -- total_len = 7 (3+4)

檢視觸發器狀態

SHOW TRIGGERS\G
-- 或查詢元數據
SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'your_db';

輸出關鍵欄位：Trigger、Event、Table、Statement、Timing、Created、sql_mode。

範例二：帳戶變更審計日誌（企業級實戰）

-- 審計表
CREATE TABLE `audit_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `account_id` INT NOT NULL,
    `old_balance` DECIMAL(12,2),
    `new_balance` DECIMAL(12,2),
    `changed_by` VARCHAR(128) NOT NULL,
    `changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 觸發器：UPDATE 後記錄變更
CREATE TRIGGER `trg_account_upd_audit`
AFTER UPDATE ON `accounts`
FOR EACH ROW
INSERT INTO `audit_log` (`account_id`, `old_balance`, `new_balance`, `changed_by`)
VALUES (OLD.`id`, OLD.`balance`, NEW.`balance`, CURRENT_USER());

效果：每次帳戶餘額變動，自動記錄帳戶ID、舊餘額、新餘額、操作用戶、時間戳，完全透明於應用層。

補充：刪除觸發器的反向邏輯

上例僅處理 INSERT/UPDATE。完整審計需補充 DELETE 觸發器：

CREATE TRIGGER `trg_data_del_len`
BEFORE DELETE ON `data`
FOR EACH ROW
UPDATE `chars` SET `total_len` = `total_len` - CHAR_LENGTH(OLD.`name`);

使用 BEFORE DELETE 確保在資料列實際刪除前仍可讀取 OLD 值。

刪除觸發器

DROP TRIGGER [IF EXISTS] `trigger_name`;

效能與限制提醒

• 觸發器內避免複雜查詢/事務：會延長鎖持有時間，影響併發。
• 不支援 CALL 存儲過程（舊版本），需內聯 SQL。
• 遞歸觸發風險：觸發器內修改同表會再次觸發 → 死循環，MySQL 會報錯阻止。
• 備份/還原：mysqldump --triggers（預設開啟）包含觸發器定義。
• 權限：需 TRIGGER 權限（MySQL 5.1.6+ 獨立權限，舊版需 SUPER）。

最佳實踐棧單

1. 命名規範：trg_{table}_{timing}_{event}_{purpose} 如 trg_orders_aft_ins_audit
2. 單一職責：一個觸發器只做一件事，複雜邏輯拆分多個觸發器或移至存儲過程。
3. 測試驗證：單元測試覆蓋 INSERT/UPDATE/DELETE 各路徑，確認審計表資料正確。
4. 文檔化：在資料庫設計文檔中列出所有觸發器、用途、觸發條件。

善用觸發器實現審計、衍生欄維護、軟刪除標記等橫切關注點，能大幅簡化應用層代碼，確保資料層面的業務不變量始終成立。