Triggers won't help you here. There's nothing to make it fire!
What you need is a (scheduler) job. So your process is:
- Create a procedure to transfer the data from the current to the history table
- Submit a job which runs daily/weekly/monthly/... calling that procedure
The procedure could be a simple insert+delete. For example:
create table t (
x int,
up_dt date
);
create table t_hist as
select * from t;
insert into t
select rownum, sysdate-400+rownum from dual connect by level <= 400;
create index i on t(up_dt);
create or replace procedure arch_t is
begin
insert into t_hist
select * from t
where up_dt <= add_months(sysdate, -12);
delete t
where up_dt <= add_months(sysdate, -12);
end arch_t;
/
select count(*) from t;
COUNT(*)
400
select count(*) from t_hist;
COUNT(*)
0
exec arch_t;
select count(*) from t;
COUNT(*)
366
select count(*) from t_hist;
COUNT(*)
34
If your table is "large" so you're deleting millions of rows+ on each run you may want to look at partitioning. If you partition the primary table on update_date, instead of a delete can do a "drop partition". This will be faster and generate less redo.
Once you're happy with the procedure, you can schedule it with something like:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ARCH_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'ARCH_T',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=WEEKLY',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => '');
DBMS_SCHEDULER.enable(
name => 'ARCH_JOB');
END;
/
And Oracle will automatically run it according to the repeat_interval (weekly here).
For more about the scheduler, see:
http://docs.oracle.com/database/121/ADMIN/schedover.htm#ADMIN033 https://oracle-base.com/articles/10g/scheduler-10g