CODEWITHSUNDEEP
oracle-database
user13664104
user13664104

Reputation:

how to call job after insert into table oracle?

i have a table i want call a job after two minute from every insert into table ?

Upvotes: 0

Views: 826

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

As you already know, there are quite a few "ifs" (see comments).

Anyway, you asked a question and here's one option which answers it.

Sample table:

SQL> create table test (id number, name varchar2(20), datum date);

Table created.

A dummy procedure which doesn't do anything smart, just re-inserts the same row, increments ID column value and sets a new timestamp to test whether it fired 2 minutes after the insert):

SQL> create or replace procedure p_test (par_id test.id%type) is
  2  begin
  3    insert into test (id, name, datum)
  4      select id + 1,
  5             name,
  6             sysdate
  7      from test
  8      where id = par_id;
  9  end;
 10  /

Procedure created.

Trigger creates a database job using simple DBMS_JOB package; it calls previously created P_TEST procedure 2 minutes after the insert.

SQL> create or replace trigger trg_ai_test
  2    after insert on test
  3    for each row
  4  declare
  5    l_job number;
  6  begin
  7     dbms_job.submit (
  8        job         => l_job,
  9        what        =>    'begin p_test ('
 10                       || :new.id
 11                       || '); end;',
 12        next_date   => sysdate + 2 / (24 * 60),
 13        interval    => null);
 14  end trg_ai_test;
 15  /

Trigger created.

Setting date format (so that you'd know what is what in the datum column):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Testing:

SQL> select * from test;

no rows selected
    
SQL> insert into test (id, name, datum) values (1, 'Little', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME                 DATUM
---------- -------------------- -------------------
         1 Little               01.11.2020 11:12:33

SQL>

Several minutes later:

SQL> select * from test order by datum;

        ID NAME                 DATUM
---------- -------------------- -------------------
         1 Little               01.11.2020 11:12:33
         2 Little               01.11.2020 11:14:36
         3 Little               01.11.2020 11:16:36

SQL>

So, yes - it is firing every 2 minutes and does something. See if it helps.

Upvotes: 1

Related Questions