MySQL 觸發器實戰精通：建立、查看、刪除與層疊更新實戰範例

觸發器基本概念

• 定義：特定表發生 INSERT/UPDATE/DELETE 事件時，自動執行的程式碼區塊。
• 觸發時機：BEFORE（寫入前，可修改新值） / AFTER（寫入後，可基於已寫入值操作）。
• 粒度：FOR EACH ROW（逐列觸發，MySQL 僅支援此模式）。
• 版本：MySQL 5.0+ 引入，5.7/8.0 持續增強。

語法結構

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL 陳述式（可多條，用 ; 分隔）
END;

實戰範例：自動統計字元總數（層疊更新）

-- 1. 建立資料表
CREATE TABLE data (name VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE chars (total_chars INT UNSIGNED DEFAULT 0) ENGINE=InnoDB;
INSERT INTO chars (total_chars) VALUES (0);

-- 2. 建立 AFTER INSERT 觸發器
DELIMITER //
CREATE TRIGGER trg_data_after_insert
AFTER INSERT ON data
FOR EACH ROW
BEGIN
    UPDATE chars SET total_chars = total_chars + CHAR_LENGTH(NEW.name);
END//
DELIMITER ;

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

關鍵字 NEW / OLD 詳細說明

BEFORE 觸發器中可修改 NEW.xxx 以攔截/轉換寫入值（如自動加密、格式化、預設值補充）。

查看與管理觸發器

-- 列出所有觸發器
SHOW TRIGGERS\G

-- 查看特定資料庫觸發器
SHOW TRIGGERS FROM mydb\G

-- 查看建立語句
SHOW CREATE TRIGGER trg_data_after_insert\G

-- 刪除觸發器
DROP TRIGGER IF EXISTS trg_data_after_insert;

進階實戰：審計日誌觸發器

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    operation ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(128)
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER trg_products_audit
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, new_data, changed_by)
    VALUES ('products', 'INSERT', CAST(JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'price', NEW.price) AS JSON), CURRENT_USER());
END//

CREATE TRIGGER trg_products_audit_upd
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_by)
    VALUES ('products', 'UPDATE', 
        CAST(JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'price', OLD.price) AS JSON),
        CAST(JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'price', NEW.price) AS JSON),
        CURRENT_USER());
END//

CREATE TRIGGER trg_products_audit_del
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, changed_by)
    VALUES ('products', 'DELETE', 
        CAST(JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'price', OLD.price) AS JSON),
        CURRENT_USER());
END//
DELIMITER ;

效能與限制注意事項

• 觸發器在同一交易內執行，觸發器錯誤 → 整個交易回滾。
• 避免觸發器中執行耗時操作（跨表大量更新、外部呼叫），拖慢寫入延遲。
• 觸發器不支援 CALL 預存程序（MySQL 限制），邏輯需內聯撰寫。
• 遞迴觸發：觸發器修改同表 → 再次觸發 → 無限循環。MySQL 禁止遞迴觸發（報錯）。
• 備份還原：mysqldump --triggers 包含觸發器定義。
• 複寫：Row-based 複寫下，觸發器在 Slave 同樣執行；Statement-based 可能導致不一致，建議 binlog_format=ROW。

何時使用觸發器 vs 應用層邏輯？