MySQL 觸發器實戰：數據遷移同步、審計日誌、刪除備份、避坑指南

前言

觸發器是針對表級 DML（INSERT/UPDATE/DELETE）自動執行的存儲程序，常用於數據遷移同步、審計追蹤、衍生列計算、跨表同步。本文通過 SQL Server→MySQL 遷移案例、審計日誌、刪除備份三大場景，講解觸發器編寫技巧與常見陷阱。

場景一：SQL Server 觸發器遷移至 MySQL

原 SQL Server 觸發器

ALTER TRIGGER [trg_risks] ON dbo.projectrisk FOR INSERT, UPDATE AS
BEGIN
  UPDATE projectrisk SET classification = 
    CASE WHEN calc>=9 THEN 3 WHEN calc<9 AND calc>=4 THEN 2 WHEN calc<4 THEN 1 END
  FROM (SELECT inserted.id, inserted.possibility*inserted.severity AS calc FROM inserted) T1
  WHERE projectrisk.id = T1.id
END

MySQL 觸發器語法差異

• 每個觸發器僅支援單一事件：需建立兩個觸發器（INSERT/UPDATE 分離）
• 無 inserted 表，使用 NEW 引用新行
• BEFORE 觸發器可直接修改 NEW.column，避免遞歸更新

錯誤嘗試（陷阱！）：AFTER 觸發器更新同表 → 死循環

CREATE TRIGGER trg_risks_insert AFTER INSERT ON projectrisk FOR EACH ROW
  UPDATE projectrisk SET classification = ... WHERE id = NEW.id;  -- 觸發 UPDATE 觸發器 → 無限遞歸

正確寫法：BEFORE 觸發器直接賦值

DELIMITER &&
CREATE TRIGGER trg_risks_insert BEFORE INSERT ON projectrisk FOR EACH ROW
BEGIN
  SET NEW.classification = CASE
    WHEN NEW.possibility * NEW.severity >= 9 THEN 3
    WHEN NEW.possibility * NEW.severity >= 4 THEN 2
    ELSE 1
  END;
END &&

CREATE TRIGGER trg_risks_update BEFORE UPDATE ON projectrisk FOR EACH ROW
BEGIN
  SET NEW.classification = CASE
    WHEN NEW.possibility * NEW.severity >= 9 THEN 3
    WHEN NEW.possibility * NEW.severity >= 4 THEN 2
    ELSE 1
  END;
END &&
DELIMITER ;

核心原則： 需修改同表數據時，必須用 BEFORE 觸發器 + SET NEW.col，切勿用 AFTER 觸發器再 UPDATE 同表。

場景二：同步備份表（INSERT 同步）

DELIMITER $$
CREATE TRIGGER InsertOPM_Alarm_trigger BEFORE INSERT ON OPM_Alarm FOR EACH ROW
BEGIN
  INSERT INTO OPM_Alarm_copy (
    AlarmId, AlarmCode, AlarmTypeId, AlarmLevelId, AlarmObjectCode,
    AlarmStatus, AlarmHandleUser, AlarmHandleTime, ADDTIME,
    ParkUserId, BerthCode, BargainOrderCode, BerthStartTime
  ) VALUES (
    NEW.AlarmId, NEW.AlarmCode, NEW.AlarmTypeId, NEW.AlarmLevelId, NEW.AlarmObjectCode,
    NEW.AlarmStatus, NEW.AlarmHandleUser, NEW.AlarmHandleTime, NEW.ADDTIME,
    NEW.ParkUserId, NEW.BerthCode, NEW.BargainOrderCode, NEW.BerthStartTime
  );
END;
$$
DELIMITER ;

場景三：刪除操作審計與備份

1. 刪除前備份到副本表

DELIMITER $$
CREATE TRIGGER BackupOPM_Alarm_trigger BEFORE DELETE ON OPM_Alarm FOR EACH ROW
BEGIN
  INSERT INTO OPM_Alarm_copy (AlarmId, AlarmCode, ..., BerthStartTime)
  VALUES (OLD.AlarmId, OLD.AlarmCode, ..., OLD.BerthStartTime);
END;
$$
DELIMITER ;

2. 刪除後記錄審計日誌（用戶、IP、刪除前完整數據、時間）

DELIMITER $$
CREATE TRIGGER SYS_OPM_trigger AFTER DELETE ON OPM_Alarm FOR EACH ROW
BEGIN
  DECLARE str VARCHAR(40000);
  SET str = CONCAT(
    OLD.AlarmId, '@', OLD.AlarmCode, '@', OLD.AlarmTypeId, '@', OLD.AlarmLevelId, '@',
    OLD.AlarmObjectCode, '@', OLD.AlarmStatus, '@', OLD.AlarmHandleUser, '@', OLD.AlarmHandleTime, '@',
    OLD.AddTime, '@', OLD.ParkUserId, '@', OLD.BerthCode, '@', OLD.BargainOrderCode, '@', OLD.BerthStartTime
  );
  INSERT INTO OPM_AlarmAction_log (UserName, Client_IP, Delete_before_key, Delete_Date)
  VALUES (
    SUBSTRING_INDEX(USER(), '@', 1),      -- 當前用戶名
    SUBSTRING_INDEX(USER(), '@', -1),     -- 客戶端 IP
    str,                                  -- 序列化完整舊行
    NOW()
  );
END;
$$
DELIMITER ;

四、觸發器管理命令

五、避坑指南與最佳實踐

1. BEFORE 修改 NEW，AFTER 只讀：同表修改必須 BEFORE + SET NEW.col
2. 避免觸發器鏈式調用：觸發器中修改其他表若也有觸發器，易形成難以追蹤的鏈路
3. 大批量操作性能殺手：百萬級批量 INSERT/UPDATE 會逐行觸發，嚴重拖慢速度 → 考慮應用層邏輯或暫時禁用觸發器
4. 事務邊界：觸發器在觸發語句的事務內執行，觸發器報錯會導致主語句回滾
5. 備份/遷移注意：mysqldump --triggers 導出觸發器，導入時需確保 DEFINER 賬號存在或用 --skip-definer
6. 版本差異：MySQL 5.7+ 支援同表多個同事件觸發器（FOLLOWS/PRECEDES 控制順序）