SymmetricDS双向同步场景下Oracle触发器实时数据修正需求

核心思路 #

当SymmetricDS把PostgreSQL的插入/更新数据同步到Oracle后，Oracle的触发器需要实时修正指定列的值，同时必须注意避免触发SymmetricDS的反向同步——这是重中之重，不然修正后的数据又会被同步回PostgreSQL，陷入循环同步的死胡同。

具体实现步骤 #

1. 先区分SymmetricDS的同步操作 #

SymmetricDS在同步数据时，一般会用专用的数据库用户，或者设置特定的会话属性。你可以通过以下方式识别：

• 检查当前会话用户是否为SymmetricDS的同步专用用户：SYS_CONTEXT('USERENV', 'SESSION_USER')
• 部分SymmetricDS版本支持自定义会话变量，比如SYM_TRIGGER_DISABLE，也可以用来标记同步操作

2. 编写Oracle触发器示例 #

假设我们要处理的同步表是business_data，需要把status列的'INIT'转换为'READY'，把timestamp_col从PostgreSQL的UTC时间转换为Oracle的本地时区时间。触发器代码如下：

CREATE OR REPLACE TRIGGER trg_sync_data_correct
AFTER INSERT OR UPDATE ON business_data
FOR EACH ROW
DECLARE
    current_user VARCHAR2(80);
BEGIN
    -- 获取当前执行操作的数据库用户
    current_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
    
    -- 仅当数据来自SymmetricDS同步时执行修正逻辑
    IF current_user = 'SYM_SYNC_USER' THEN -- 替换成你的SymmetricDS同步用户名
        -- 修正status列值
        IF :NEW.status = 'INIT' THEN
            :NEW.status := 'READY';
        END IF;
        
        -- 转换时间列时区（假设PostgreSQL传的是UTC时间，转成Oracle的Asia/Shanghai时区）
        :NEW.timestamp_col := FROM_TZ(:NEW.timestamp_col, 'UTC') AT TIME ZONE 'Asia/Shanghai';
        
        -- 其他需要修正的列逻辑可以在这里追加
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- 可以根据业务需求添加异常处理，比如写入日志表
        RAISE; -- 如果不想中断同步，也可以改成静默处理
END;
/

3. 避免循环同步的关键设置 #

• 在SymmetricDS的同步规则配置里，针对Oracle到PostgreSQL的方向，忽略触发器修改的列，或者把这些列的sync_on_update设为false。
• 也可以在触发器里设置SymmetricDS的专属上下文变量，告知它不要捕获这次修改：

  DBMS_SESSION.SET_CONTEXT('SYM_CONTEXT', 'SYM_TRIGGER_DISABLE', 'true');
  -- 执行完修正逻辑后恢复
  DBMS_SESSION.SET_CONTEXT('SYM_CONTEXT', 'SYM_TRIGGER_DISABLE', 'false');
  

4. 测试验证流程 #

• 在PostgreSQL插入一条测试数据：INSERT INTO business_data (id, status, timestamp_col) VALUES (1001, 'INIT', '2024-05-20 14:30:00 UTC');
• 查看Oracle中该条数据的status是否变为'READY'，timestamp_col是否转换为本地时区时间
• 确认修正后的数据没有被同步回PostgreSQL，避免循环

注意事项 #

• 触发器逻辑要尽量简洁，复杂运算会拖慢同步性能
• 一定要处理异常，不然触发器报错会直接导致SymmetricDS同步失败
• 定期查看触发器的执行日志，确保修正逻辑稳定运行

内容的提问来源于stack exchange，提问作者antropka