The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Thanks for the question.

Asked: May 16, 2016 - 4:30 pm UTC

Last updated: May 17, 2016 - 3:56 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

How to call a scheduler from trigger?

and Chris said...

Calls to DBMS_scheduler.create_job issue an implicit commit.

You can't commit inside a trigger normally, so this fails. You can get around this by using an autonomous_transaction:

SQL> create table t (
  2    x int
  3  );

Table created.

SQL>
SQL> create or replace trigger trig
  2    after insert on t
  3  declare
  4    pragma autonomous_transaction;
  5  begin
  6
  7    dbms_scheduler.create_job (
  8      job_name        => 'test_job',
  9      job_type        => 'PLSQL_BLOCK',
 10      job_action      => 'begin null end;',
 11      start_date      => systimestamp,
 12      repeat_interval => 'freq=daily;',
 13      enabled         => true);
 14
 15  end;
 16  /

Trigger created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL>
SQL> select job_name from user_scheduler_jobs;

JOB_NAME
------------------------------
TEST_JOB

Be sure you really want to do this! If you rollback the insert, the job will still exist. Continuing the example above:

SQL> rollback;

Rollback complete.

SQL>
SQL> select * from t;

no rows selected

SQL>
SQL> select job_name from user_scheduler_jobs;

JOB_NAME
------------------------------
TEST_JOB

So this could lead to some unexpected outcomes

Rating

(2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Use DBMS_JOB instead of the DBMS_SCHEDULER

Martin Rose, May 17, 2016 - 1:37 pm UTC

> Be sure you really want to do this! If you rollback the insert, the job will still exist

If he uses DBMS_JOB instead of the Scheduler, he won't have this problem as it takes part in transactions. Ie; the job is only actually launched at commit time, and not before. If the transaction rolls back, so do all the jobs.

Martin Rose, May 17, 2016 - 1:41 pm UTC

Belay that last comment. As it's inside an autonomous transaction, it will naturally commit anyway outside the main transaction.

Fine technique otherwise.

May 17, 2016 - 3:56 pm UTC

No, you were right the first time! You can use dbms_job if you need it to be transactional and you don't need the scheduler enhancements.

Assuming you remove the autonomous transaction that is...

SQL> create table t (
  2    x int
  3  );

Table created.

SQL>
SQL> create or replace trigger trig
  2    after insert on t
  3  declare
  4    job int;
  5  begin
  6    dbms_job.submit (
  7      job       => job,
  8      what      => 'begin null; end;',
  9      next_date => sysdate,
 10      interval  => 'sysdate + 1/24'
 11     );
 12  end;
 13  /

Trigger created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL>
SQL> select what from user_jobs;

WHAT
-------------------------------------------

begin null; end;

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select * from t;

no rows selected

SQL>
SQL> select what from user_jobs;

no rows selected

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Use DBMS_JOB instead of the DBMS_SCHEDULER

More to Explore

PL/SQL demos

PL/SQL docs

Call scheduler from a trigger

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Use DBMS_JOB instead of the DBMS_SCHEDULER

More to Explore

PL/SQL demos

PL/SQL docs