MySQL 觸發器 (Trigger) 完整建立與管理指南

MySQL 觸發器是針對特定表的 INSERT、UPDATE、DELETE **行級**操作自動執行的程式碼。本文詳細說明命名規範、語法、NEW/OLD 存取、建立/刪除/查看管理指令。

命名規範建議

t_{trigger_time}_{table_name}_{event}
-- 範例:
t_before_users_insert   -- trg_before_users_ins
t_after_orders_update   -- trg_after_orders_upd
t_before_products_delete

• t_ / trg_：觸發器前綴
• before/after：觸發時機
• table_name：目標表
• ins/upd/del 或 i/u/d：事件類型

完整建立語法

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name
FOR EACH ROW
BEGIN
    trigger_statement;
END;

參數說明

NEW 與 OLD 伪表 (核心存取方式)

存取方式：NEW.column_name、OLD.column_name

實戰範例：審計日誌、資料同步、業務驗證

範例 1：插入審計日誌 (AFTER INSERT)

DELIMITER //
CREATE TRIGGER trg_after_users_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, new_data, created_at)
    VALUES ('users', 'INSERT', NEW.id, 
            JSON_OBJECT('name', NEW.name, 'email', NEW.email), 
            NOW());
END //
DELIMITER ;

範例 2：更新前驗證/修正 (BEFORE UPDATE)

DELIMITER //
CREATE TRIGGER trg_before_products_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    -- 價格不得為負
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '價格不能為負數';
    END IF;
    
    -- 自動更新修改時間
    SET NEW.updated_at = NOW();
    
    -- 記錄價格變更歷史
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END //
DELIMITER ;

範例 3：刪除前備份/級聯處理 (BEFORE DELETE)

DELIMITER //
CREATE TRIGGER trg_before_orders_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    -- 歸檔刪除訂單
    INSERT INTO orders_archive SELECT * FROM orders WHERE id = OLD.id;
    
    -- 檢查是否有關聯付款 (拒絕刪除)
    IF EXISTS (SELECT 1 FROM payments WHERE order_id = OLD.id AND status = 'paid') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已付款訂單不可刪除';
    END IF;
END //
DELIMITER ;

管理指令大全

查看觸發器

-- 列出所有觸發器
SHOW TRIGGERS;
-- 或特定資料庫
SHOW TRIGGERS FROM db_name;

-- 查看特定觸發器建立語句
SHOW CREATE TRIGGER trg_after_users_insert;

-- 查看觸發器元資料
SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'db_name';

刪除觸發器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
-- 範例
DROP TRIGGER IF EXISTS trg_after_users_insert;

權限需求

• 建立/刪除：TRIGGER 權限 (通常需 SUPER 權限 MySQL 5.7 前)
• MySQL 5.7+：TRIGGER 權限獨立，無需 SUPER
• 執行時：觸發器以 DEFINER 權限執行 (預設建立者)

重要限制與注意事項

• 無陳述式級觸發器：MySQL 僅支援 FOR EACH ROW
• 無 INSTEAD OF 觸發器：不能攔截並替換操作 (視圖可用)
• 不能呼叫會隱式提交的語句：CREATE/DROP/ALTER TABLE, LOCK TABLES, START TRANSACTION 等
• 不能使用動態 SQL：PREPARE/EXECUTE 在觸發器中受限
• 避免長時間運算：觸發器在原交易中執行，會拉長鎖定時間
• 巢狀觸發：觸發器內操作觸發其他觸發器，Max max_sp_recursion_depth
• 複製/備份：mysqldump --triggers 預設包含

除錯技巧

-- 1. 簡單測試
INSERT INTO users (name, email) VALUES ('test', 'test@example.com');
SELECT * FROM audit_log ORDER BY id DESC LIMIT 1;

-- 2. 檢查錯誤日誌
-- SIGNAL 拋出的錯誤會顯示在客戶端

-- 3. 追蹤執行 (需開啟 general_log)
SET GLOBAL general_log = 1;
-- 執行操作
SET GLOBAL general_log = 0;