Delete a row after certain time using trigger

Question

I want to create a trigger which deletes the rows in the table automatically after 3 months if the status is not updated.

Create Trigger to delete rows that are >90 days old

This was the reference through which I was trying to do it but could not do it. Kindly help me to figure out this.

you have created a job scheduler that through automatic run this scheduler and execute from your procedure.for more information please refer this link [https://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034] — Kiran Patil, Oct 19 '19 at 07:31

score 0 · Accepted Answer · answered Oct 19 '19 at 07:36

0

You are looking for dbms_scheduler.

begin
dbms_scheduler.create_job (
    job_name => 'DEL_BEFORE_3MONTH_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DELETE FROM YOUR_TABLE WHERE ADD_MONTHS(YOUR_DATE,3) < TRUNC(SYSDATE); COMMIT; END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=DAILY;BYHOUR=12', -- NOON
    enabled => true    
);
end;
/

Refer this document for more information on it.

Cheers!!

answered Oct 19 '19 at 07:36

Popeye

35,427
4
10
31

Thank You!!!! this solved my problem and sorry for the late reply – Qwerty Oct 28 '19 at 12:52

Delete a row after certain time using trigger

1 Answers1