Oracle SQL触发器实现：同步SALES_ORDER表15分钟数据至TEMP表

你原来的代码存在几个关键问题：

• 未指定FOR EACH ROW，默认是语句级触发器，无法访问单行的列值（比如UPDATE_DATE）
• 直接引用UPDATE_DATE而非触发行的变量（需用:NEW/:OLD前缀）
• 缺少针对增/删/改操作的具体同步逻辑

以下是完整的实现代码，假设：

1. TEMP表结构与SALES_ORDER完全一致
2. SALES_ORDER有唯一主键ORDER_ID（用于匹配TEMP表的对应记录）
3. UPDATE_DATE是记录最后更新的时间字段（INSERT/UPDATE时自动赋值）

CREATE OR REPLACE TRIGGER FETCH_DATA
AFTER INSERT OR UPDATE OR DELETE ON UFD_BASE.SALES_ORDER
FOR EACH ROW -- 行级触发器，处理每一条变动的记录
DECLARE
    v_time_threshold DATE := SYSDATE - INTERVAL '15' MINUTE;
BEGIN
    -- 处理INSERT操作：将新记录插入TEMP表（仅当更新时间在15分钟内）
    IF INSERTING THEN
        IF :NEW.UPDATE_DATE >= v_time_threshold THEN
            INSERT INTO TEMP
            VALUES (:NEW.*); -- 插入新行的所有字段，若结构不一致需显式指定字段
        END IF;
    -- 处理UPDATE操作：先删除TEMP中旧记录，再插入新记录（仅当更新时间在15分钟内）
    ELSIF UPDATING THEN
        IF :NEW.UPDATE_DATE >= v_time_threshold THEN
            DELETE FROM TEMP WHERE ORDER_ID = :OLD.ORDER_ID;
            INSERT INTO TEMP
            VALUES (:NEW.*);
        END IF;
    -- 处理DELETE操作：删除TEMP中对应记录（仅当原记录更新时间在15分钟内）
    ELSIF DELETING THEN
        IF :OLD.UPDATE_DATE >= v_time_threshold THEN
            DELETE FROM TEMP WHERE ORDER_ID = :OLD.ORDER_ID;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- 可选：捕获异常避免主表操作失败，可添加日志记录逻辑
        NULL;
END;
/

关键说明 #

• 行级触发器：必须添加FOR EACH ROW才能访问:NEW（新数据）和:OLD（旧数据）变量
• 时间判断：根据操作类型选择:NEW.UPDATE_DATE（INSERT/UPDATE）或:OLD.UPDATE_DATE（DELETE），确保只同步最近15分钟的变动
• 字段匹配：如果TEMP表结构与SALES_ORDER不一致，需将VALUES (:NEW.*)改为显式指定字段，例如：

  INSERT INTO TEMP (ORDER_ID, ORDER_DATE, AMOUNT, UPDATE_DATE)
  VALUES (:NEW.ORDER_ID, :NEW.ORDER_DATE, :NEW.AMOUNT, :NEW.UPDATE_DATE);
  

• 性能替代方案：如果SALES_ORDER数据量极大，行级触发器可能带来性能开销，可改用定时任务（DBMS_SCHEDULER）每15分钟同步一次最近15分钟的数据，示例定时任务逻辑：

  -- 创建定时任务，每15分钟执行一次同步
  BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
          job_name        => 'SYNC_SALES_TO_TEMP',
          job_type        => 'PLSQL_BLOCK',
          job_action      => '
              BEGIN
                  -- 先删除TEMP中超过15分钟的旧数据（可选）
                  DELETE FROM TEMP WHERE UPDATE_DATE < SYSDATE - INTERVAL ''15'' MINUTE;
                  -- 插入最近15分钟新增/更新的数据
                  MERGE INTO TEMP t
                  USING (SELECT * FROM UFD_BASE.SALES_ORDER WHERE UPDATE_DATE >= SYSDATE - INTERVAL ''15'' MINUTE) s
                  ON (t.ORDER_ID = s.ORDER_ID)
                  WHEN MATCHED THEN UPDATE SET t = s
                  WHEN NOT MATCHED THEN INSERT VALUES (s.*);
              END;
          ',
          start_date      => SYSDATE,
          repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
          enabled         => TRUE
      );
  END;
  /
  

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