Delaying the trigger invocation after an insert oracle

Question

Hi,

Is there a way to do this?. I found adding,

DBMS_LOCK.sleep() 

to the beginning of the trigger code by googling, but it blocks the insert itself from happening. I would like to insert the data but the trigger should be fired only after an arbitrary delay. Thanks.

Answer 1

It would help if we knew why you want this delay, and what the trigger is supposed to do after the delay. However, one possibility is to use the DBMS_JOB package in the trigger to create a job that runs at a time a little after the insert. For example:

create trigger trg
after insert on tab
for each row
declare
  jl_ob number;
begin
  dbms_job.submit
    ( job => l_job
    , what => 'myproc(:new.id);'
    , next_date => sysdate+1/24/60 -- One minute later
    );
end;

Alternatively, the trigger could insert a row into a special table, and a DBMS_JOB that runs on a schedule e.g. every 10 minutes could process rows in the table that are more than X minutes old.