MySQL 觸發器 UPDATE 觸發 INSERT 失敗排查實錄

MySQL 觸發器 UPDATE 觸發 INSERT 失敗排查實錄

需求：主表 triggletest_triggle 狀態更新時，自動寫入歷史表 triggletest。建立 AFTER UPDATE 觸發器卻報語法錯誤，最終發現是 客戶端工具 (HeidiSQL) 分隔符處理問題。

環境與表結構

-- 主表
CREATE TABLE `triggletest_triggle` (
    `id` INT(11) NOT NULL,
    `name` VARCHAR(5) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 歷史表
CREATE TABLE `triggletest` (
    `seq` INT(11) NOT NULL AUTO_INCREMENT,
    `id` INT(11) NOT NULL,
    `name` VARCHAR(5) NULL DEFAULT NULL,
    PRIMARY KEY (`seq`),
    INDEX `idx_id` (`id`)
) ENGINE=InnoDB;

-- 測試資料
INSERT INTO `triggletest_triggle` VALUES (1, 'A');

錯誤觸發器語句 (在 HeidiSQL 執行)

DROP TRIGGER IF EXISTS test1;
CREATE TRIGGER test1 AFTER UPDATE ON triggletest_triggle
FOR EACH ROW
BEGIN
    INSERT INTO triggletest(id) VALUES (NEW.id);
END

報錯：

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TRIGGER test1 AFTER UPDATE ON triggletest_triggle FOR EACH ROW BEGIN' at line 2 */

根因分析

1. 觸發器本體包含 BEGIN ... END 區塊，內部有分號 ;。
2. MySQL 預設以分號為語句結束符，導致 CREATE TRIGGER 在第一個分號處被截斷，語法不完整。
3. HeidiSQL (及許多 GUI 工具) 未正確識別觸發器/程序的完整邊界，直接按分號拆分送給伺服器。

正確解決方案：使用 DELIMITER 重新定義結束符

DELIMITER $$

DROP TRIGGER IF EXISTS test1;

CREATE TRIGGER test1
AFTER UPDATE ON triggletest_triggle
FOR EACH ROW
BEGIN
    INSERT INTO triggletest(id, name) VALUES (NEW.id, NEW.name);
END$$

DELIMITER ;

驗證觸發器生效

# 更新主表
UPDATE triggletest_triggle SET name='B' WHERE id=1;

# 查詢歷史表
SELECT * FROM triggletest;
+-----+----+------+
| seq | id | name |
+-----+----+------+
| 1   | 1  | B    |
+-----+----+------+

工具差異對照

最佳實務建議

1. 統一使用 DELIMITER $$ ... $$ 撰寫所有觸發器、預存程序、函數腳本，確保跨工具相容。
2. 版控管理：將觸發器 DDL 存為 .sql 檔，部署時用 mysql -u root -p < trigger.sql 執行，避免 GUI 工具干擾。
3. 避免在觸發器中做重邏輯：複雜業務移至應用層或異步處理，觸發器僅做輕量審計/同步。
4. 命名規範：trg_{table}_{timing}_{event}，如 trg_triggletest_triggle_au_ins_hist。
5. 檢視現有觸發器：SHOW TRIGGERS\G 或查詢 information_schema.TRIGGERS。

延伸：觸發器權限與複制

• 建立觸發器需 TRIGGER 權限 (MySQL 5.1.6+)。
• 主從複制下，觸發器在主庫觸發、從庫同步執行，需確保從庫同樣存在觸發器定義 (log_bin_trust_function_creators 相關)。
• 避免觸發器巢狀調用 (觸發器內 UPDATE 同表) 導致無限循環。

「工具不會背鍋，但開發者要懂工具的限制。」—— 記一次因分隔符導致的 30 分鐘排查。