DB2 同步觸發器實戰：記錄用戶操作至暫存表的增刪改邏輯完整實現

業務場景

需捕獲核心表 TM_USERG（用戶組）的增刪改操作，寫入暫存表 TM_USERG_TEMP，每日透過 Web Service 同步至下游系統，同步成功後清空暫存表。

三類觸發器完整代碼

1. INSERT 觸發器：直接記錄新增動作

CREATE TRIGGER LIBING.TG_USERG AFTER INSERT ON LIBING.TM_USERG
REFERENCING NEW AS NROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  DECLARE @groupId INTEGER; DECLARE @name VARCHAR(30); ... -- 變數宣告
  SET @groupId = NROW.GROUP_ID; SET @name = NROW.name; ... -- 賦值
  INSERT INTO TM_USERG_TEMP(GROUP_ID, NAME, DESCN, DDLSQL, ISPRIMARY,
    UPDATE_TIME, CREATE_TIME, CREATE_BY, UPDATE_BY, GROUP_TYPE,
    ADMIN_TYPE, APP_ID, ACTION)
  VALUES (@groupId, @name, @descn, @ddlsql, @isprimary,
    @updateTime, @createTime, @createBy, @updateBy, @groupType,
    @adminType, @appId, 'INSERT');
END

2. UPDATE 觸發器：智慧去重與狀態合併

CREATE TRIGGER TG_USERG_UPDATE AFTER UPDATE ON TM_USERG
REFERENCING NEW AS NROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  -- 變數宣告賦值同上
  -- 邏輯：優先處理同組同動作的去重
  IF EXISTS(SELECT 1 FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='UPDATE') THEN
    UPDATE TM_USERG_TEMP SET ... ACTION='UPDATE' WHERE GROUP_ID=@groupId AND ACTION='UPDATE';
  ELSEIF EXISTS(SELECT 1 FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='INSERT') THEN
    UPDATE TM_USERG_TEMP SET ... ACTION='INSERT' WHERE GROUP_ID=@groupId AND ACTION='INSERT';
  ELSE
    INSERT INTO TM_USERG_TEMP(...) VALUES (...,'UPDATE');
  END IF;
END

設計亮點：同一事務內多次 UPDATE 只保留最新；INSERT 後緊接 UPDATE 視為同一 INSERT 動作，避免下游重複處理。

3. DELETE 觸發器：三態衝突解決

CREATE TRIGGER TG_USERG_DELETE AFTER DELETE ON TM_USERG
REFERENCING OLD AS OROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  -- 變數從 OROW 賦值
  IF NOT EXISTS(SELECT 1 FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId) THEN
    INSERT ... ACTION='DELETE';  -- 純刪除
  ELSEIF EXISTS(SELECT 1 FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='INSERT') THEN
    DELETE FROM TM_USERG_TEMP WHERE GROUP_ID=@groupId AND ACTION='INSERT';  -- INSERT+DELETE 抵消
  ELSE
    UPDATE TM_USERG_TEMP SET ACTION='DELETE' WHERE GROUP_ID=@groupId;  -- UPDATE+DELETE 合併為 DELETE
  END IF;
END

狀態機邏輯：

關鍵技術點

• REFERENCING NEW/OLD AS：標準 SQL 條款，取得新/舊行欄位
• FOR EACH ROW MODE DB2SQL：行級觸發，DB2 SQL 語法模式
• BEGIN ATOMIC：原子性區塊，隱含事務邊界
• 變數以 @ 前綴（DB2 SQL PL 風格）
• EXISTS 子查詢配合條件更新/刪除，實現應用層難以做到的「同事務去重」

部署與驗證建議

1. 先在測試庫建立觸發器，模擬增刪改組合操作
2. 檢查 TM_USERG_TEMP 記錄是否符合狀態機預期
3. 同步程式讀取暫存表時按 ACTION 分派下游邏輯
4. 同步成功後 TRUNCATE TABLE TM_USERG_TEMP IMMEDIATE（或 DELETE）清空

擴展思路

• 加入 OPERATION_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP 記錄觸發時間
• 若同步頻率極高，考慮改用 CDC (Change Data Capture) 或 Q Replication 降低觸發器開銷
• 大表高頻寫入場景，評估觸發器對主表寫入延遲的影響

此模式適用於「中低頻異構同步、需業務語義去重、無 CDC 授權」的典型場景。