MySQL 觸發器實戰：AFTER UPDATE 觸發 INSERT 失敗排查與工具兼容性分析

業務場景

需對狀態變更表建立歷史記錄：當 triggletest_triggle 表更新時，自動將變更後的數據寫入 triggletest 歷史表。

表結構

CREATE TABLE `triggletest_triggle` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(5) 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) DEFAULT NULL,
  PRIMARY KEY (`seq`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB;

觸發器創建過程與報錯

-- 初始嘗試（在 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;

報錯信息：

ERROR 1064 (42000): 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

根因分析：客戶端工具分隔符處理差異

• MySQL 默認語句分隔符為 ;，觸發器體內含多條語句需臨時修改分隔符。
• HeidiSQL / Navicat / Workbench 等 GUI 工具：自帶「分隔符管理」或「批量執行」模式，**不支持在 SQL 文本中直接使用 DELIMITER 命令**，直接粘貼含 BEGIN ... END 的觸發器會因內部 ; 被誤判為語句結束而語法錯誤。
• MySQL 官方 CLI (mysql.exe / mysql)：原生支持 DELIMITER // ... // DELIMITER ; 模式，可直接執行。

正確創建姿勢（通用兼容寫法）

方式一：GUI 工具單語句模式（推薦）

在工具中**逐條執行**以下語句，不要一次性批量執行：

DROP TRIGGER IF EXISTS test1;

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

大多數現代 GUI（Navicat 15+、Workbench 8+、DBeaver）已自動識別 BEGIN ... END 塊，單條提交即可成功。

方式二：CLI 标准 DELIMITER 模式

DELIMITER //

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

DELIMITER ;

方式三：去掉 BEGIN END（單語句觸發器）

若觸發器體僅一條語句，可省略 BEGIN ... END，完美兼容所有客戶端：

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

驗證觸發器生效

-- 插入測試數據
INSERT INTO triggletest_triggle VALUES (1, 'A');

-- 觸發更新
UPDATE triggletest_triggle SET name = 'B' WHERE id = 1;

-- 查詢歷史表
SELECT * FROM triggletest;
-- 預期輸出：seq=1, id=1, name='B'

最佳實踐總結

1. 優先使用單語句觸發器：無 BEGIN/END、無分隔符煩惱、兼容性最強。
2. GUI 工具按單條提交：不要在「腳本/批量」模式下粘貼含分隔符的腳本。
3. 複雜邏輯用存儲過程：觸發器中調用 CALL sp_log_change(NEW.id, NEW.name)，保持觸發器輕量、可測試。
4. 避免觸發器鏈式調用：觸發器 → 更新另一表 → 觸發另一觸發器，極難調試且易死鎖。
5. 監控觸發器性能：SHOW TRIGGERS、INFORMATION_SCHEMA.TRIGGERS 定期審計，防止業務邏輯隱性蔓延到 DB 層。