Trigger to continuously get last 15 mins of data in Oracle Sql

Question

I have a table called SALES_ORDER.Data gets refreshed and new data keeps coming in in this table. I want to write a trigger to fetch last 15 minutes of data from this table and insert it into another backup table/staging table(maybe called TEMP) .

This is what I am trying

CREATE OR REPLACE TRIGGER FETCH_DATA 
AFTER DELETE OR INSERT OR UPDATE ON UFD_BASE.SALES_ORDER 
BEGIN
  IF UPDATE_DATE > sysdate - interval '15' minute
  ...
  NULL;
END;

but its not correct or complete. I dont have much knowledge regarding triggers or sql. Can anybody please help me figure out how to do this?

Answer 1

Trigger isn't the right tool to do that. It fires upon certain action on a table - when row(s) get inserted, updated or deleted - not every 15 minutes.

For such a purpose, you should

• create a stored procedure which does smart part of the job
• create a database job which runs that procedure every 15 minutes

Procedure would then be e.g.

create or replace procedure p_insert is
begin
  insert into another_table
    select * from this_table
    where update_date > sysdate - interval '15' minute;
end;
/

To schedule it, use dbms_scheduler (or dbms_job, if you have really old Oracle database version), e.g.

begin
   dbms_scheduler.create_job (
      job_name         => 'insert_data',
      job_type         => 'PLSQL_BLOCK',
      job_action       => 'begin p_insert; end;',
      start_date       =>
         to_timestamp_tz ('20.03.2024 13:00 Europe/Zagreb',
                          'dd.mm.yyyy hh24:mi TZR'),
      repeat_interval  => 'freq=minutely; interval=15;',
      enabled          => true,
      comments         => 'insert data into another table');
end;
/