Oracle数据库两类触发器场景相关技术咨询

Oracle的常规DML触发器（基于表的增删改事件触发）不支持按固定时间间隔触发，触发器仅响应表上的数据操作事件（如INSERT/UPDATE/DELETE），而非定时执行。

要实现每2小时删除插入时间超过2小时的行，正确方式是使用Oracle的定时任务工具：

• 推荐用DBMS_SCHEDULER（Oracle 10g及以上的标准工具）创建定时作业，示例逻辑如下：
  1. 先创建执行删除操作的存储过程：

  CREATE OR REPLACE PROCEDURE purge_old_table1_rows AS
  BEGIN
    DELETE FROM table_1 
    WHERE time_created < SYSTIMESTAMP - INTERVAL '2' HOUR;
    COMMIT;
  END;
  /
  

  2. 再创建每2小时执行一次的调度作业：

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'PURGE_TABLE1_OLD_ROWS',
      job_type        => 'STORED_PROCEDURE',
      job_action      => 'purge_old_table1_rows',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
      enabled         => TRUE
    );
  END;
  /
  

• 也可使用旧版DBMS_JOB，但DBMS_SCHEDULER功能更完善，是官方推荐的替代方案。

Oracle没有直接提供同时满足「超时5分钟」和「行数超500」双条件触发的现成包，但可以通过触发器+定时任务+辅助表的组合方案实现需求：

实现步骤： #

1. 创建辅助记录表：用于记录上次生成CSV的时间，提升条件检查效率

CREATE TABLE csv_generation_log (
  last_gen_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  row_count_at_gen NUMBER
);
-- 初始化记录
INSERT INTO csv_generation_log VALUES (SYSTIMESTAMP - INTERVAL '1' HOUR, 0);
COMMIT;

2. 编写生成CSV的存储过程：用UTL_FILE包实现数据库端生成CSV（需先配置数据库目录对象）

CREATE OR REPLACE PROCEDURE generate_table2_csv AS
  v_file UTL_FILE.FILE_TYPE;
  v_last_gen_time TIMESTAMP;
  v_current_count NUMBER;
BEGIN
  -- 获取上次生成时间和当前表行数
  SELECT last_gen_time INTO v_last_gen_time FROM csv_generation_log;
  SELECT COUNT(*) INTO v_current_count FROM table_2;

  -- 检查触发条件：满足任一即可执行
  IF (SYSTIMESTAMP - v_last_gen_time > INTERVAL '5' MINUTE) OR (v_current_count > 500) THEN
    -- 打开文件（需先创建目录并授权）
    v_file := UTL_FILE.FOPEN('CSV_OUTPUT_DIR', 'table2_data_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS') || '.csv', 'W');
    
    -- 写入表头
    UTL_FILE.PUT_LINE(v_file, 'TIME_CREATED,SEQ_NO');
    
    -- 写入数据行
    FOR rec IN (SELECT time_created, seq_no FROM table_2) LOOP
      UTL_FILE.PUT_LINE(v_file, TO_CHAR(rec.time_created, 'YYYY-MM-DD HH24:MI:SS') || ',' || rec.seq_no);
    END LOOP;
    
    -- 关闭文件
    UTL_FILE.FCLOSE(v_file);
    
    -- 更新日志表
    UPDATE csv_generation_log 
    SET last_gen_time = SYSTIMESTAMP, row_count_at_gen = v_current_count;
    COMMIT;
    
    -- 可选：生成后清空table_2，避免重复统计旧数据
    -- TRUNCATE TABLE table_2;
  END IF;
END;
/

3. 配置双重触发机制：
   • DML触发器：在table_2插入数据后立即检查条件，无需等待定时任务

   CREATE OR REPLACE TRIGGER trigger_table2_check_csv
   AFTER INSERT ON table_2
   BEGIN
     generate_table2_csv;
   END;
   /
   

   • 定时任务兜底：防止长时间无插入导致超时条件无法触发，每1分钟检查一次（可调整间隔）

   BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
       job_name        => 'CHECK_TABLE2_CSV',
       job_type        => 'STORED_PROCEDURE',
       job_action      => 'generate_table2_csv',
       start_date      => SYSTIMESTAMP,
       repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
       enabled         => TRUE
     );
   END;
   /
   

关键说明： #

• 使用UTL_FILE前需先创建数据库目录并授权：CREATE DIRECTORY CSV_OUTPUT_DIR AS '/path/to/your/csv/folder';，再执行GRANT READ, WRITE ON DIRECTORY CSV_OUTPUT_DIR TO your_user;
• 若无需保留历史数据，生成CSV后可清空table_2，避免后续统计行数过大影响性能。

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