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) AS T1
  WHERE projectrisk.id = T1.id
END

MySQL 改寫關鍵差異

• MySQL 觸發器**單事件單觸發器**，需拆分為 INSERT/UPDATE 兩個
• 無 inserted 表，用 NEW 訪問新行
• 避免在 AFTER 觸發器中 UPDATE 同表（會遞歸觸發死循環）

錯誤寫法（死循環）

CREATE TRIGGER trg_risks_ai AFTER INSERT ON projectrisk FOR EACH ROW
  UPDATE projectrisk SET classification = ... WHERE id = NEW.id;  -- 觸發 AFTER UPDATE → 死循環

正確寫法：使用 BEFORE 直接修改 NEW

DELIMITER //

CREATE TRIGGER trg_risks_bi
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_bu
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** 修改 NEW；關聯表同步、日誌記錄、異步通知 → **AFTER** 讀取 NEW。

二、場景二：業務表異動審計日誌（同步備份 + 刪除追蹤）

2.1 INSERT 同步備份表

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;

2.2 DELETE 前備份 + 操作審計日誌

-- 刪除前備份原數據
CREATE TRIGGER BackupOPM_Alarm_trigger
BEFORE DELETE ON OPM_Alarm
FOR EACH ROW
BEGIN
  INSERT INTO OPM_Alarm_copy (...) VALUES (OLD.AlarmId, OLD.AlarmCode, ...);
END;

-- 審計日誌：記錄誰、從哪裡、刪除了什麼、何時
CREATE TRIGGER AuditOPM_Alarm_trigger
AFTER DELETE ON OPM_Alarm
FOR EACH ROW
BEGIN
  DECLARE log_str VARCHAR(4000);
  SET log_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
    log_str,
    NOW()
  );
END;

三、場景三：跨庫/異構同步架構模式

+----------------+     Trigger (AFTER)     +------------------+
|  核心業務庫     |  -------------------->  |  同步/審計庫      |
|  (InnoDB)      |  INSERT/UPDATE/DELETE   |  (歸檔、報表、ES) |
+----------------+                         +------------------+
      |                                            ^
      |  Binlog (Canal/Maxwell)                    |  Trigger 寫入
      v                                            |
+----------------+                         +------------------+
|  異構目標庫     |  <-------------------   |  消費端解析寫入   |
|  (ClickHouse,  |  Binlog 事件流           |  (異步、高吞吐)   |
|  Elasticsearch)|                         +------------------+
+----------------+

• **觸發器適用**：同實例、低延遲、強一致性要求、表級同步
• **Binlog 適用**：跨實例、高吞吐、最終一致性、全庫同步
• **混合架構**：核心表用觸發器強一致，非核心表走 Binlog 異步

四、觸發器效能與維護建議

1. 保持輕量：單表觸發器邏輯 < 50 行，避免複雜計算
2. 批量操作影響：INSERT ... VALUES (...),(...),(...) 會逐行觸發，大批量導入時考慮暫時禁用觸發器
3. 禁用/啟用：MySQL 無原生開關，需 DROP TRIGGER / 重建，或用會話變量標誌 @DISABLE_TRIGGER 在觸發器首行判斷
4. 監控指標：SHOW GLOBAL STATUS LIKE 'Trigger%';（需 8.0+）觀察觸發次數、耗時
5. 版本控制：觸發器定義納入 SQL 遷移腳本（Flyway/Liquibase）管理

五、總結

觸發器雖強大，但**隱式邏輯難排查**、**增加寫入延遲**、**備份恢復需同步觸發器定義**。架構選型時優先考慮**應用層顯式寫入**或**Binlog 異步同步**，觸發器作為輔助手段用於強一致性要求極高的核心表。