Thanks for the question, Balakrishna.

Asked: June 21, 2016 - 1:33 pm UTC

Last updated: February 28, 2017 - 5:09 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

hi Tom,

1)How to call stored function inside the trigger?
2)Is it possible to call stored function,stored procedure inside the trigger at a time, please can show with an example ?

and Chris said...

1. The same way you call a procedure or function within other PL/SQL

2. Yes.

create table t (
  x int
);
create or replace function f (p int)
  return int as 
begin
  return p + 1;
end;
/

create or replace procedure p (p int) is
begin
  dbms_output.put_line('Input = ' || p);
end p;
/

create or replace trigger trig 
before insert on t
declare
  l int;
begin
  l := f(1);
  p(l);
end;
/

SQL> insert into t values (1);
Input = 2

1 row created.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

praveendarla, February 28, 2017 - 4:47 am UTC

Is that possible to use trigger inside a package . With out using procedures if not why it is not introduced in Oracle

February 28, 2017 - 5:09 am UTC

A trigger is just a PL/SQL block (ie, code encompassed by begin-end).

It can call procedures, functions, packages etc etc just like any other PL/SQL block. It is however bound to an "event" of some sort, eg a delete, or an insert, or a DDL statement, so it would not make sense to have a trigger *definition* that it within a procedure etc.

Jess, December 17, 2019 - 7:40 pm UTC

Dear Oracle Masters,

How does one call run_job from inside a trigger?

I can run the following successfully:

exec dbms_scheduler.run_job(job_name => 'MY_USER.MY_JOB');

However, when I put it into a trigger (tried both ways, below), the trigger compiles, but then fails to execute:

create or replace trigger my_user.my_trigger after insert on my_user.my_table
declare
--  v_job varchar2(110) := 'dbms_scheduler.run_job(job_name => ''MY_USER.MY_JOB'')';

begin
    EXECUTE IMMEDIATE 'dbms_scheduler.run_job(job_name => ''MY_USER.MY_JOB'')';

--    execute immediate v_job;
end;
/

Trying to insert a record into the table dies with
ORA-00900: invalid SQL statement
ORA-06512: at "MY_USER.MY_TRIGGER", line 6
ORA-04088: error during execution of trigger 'MY_USER.MY_TRIGGER'

What is the correct way of kicking off a job from a trigger (which is what needs doing given the constraints of the current design)?

Thank you!

Jess, December 18, 2019 - 12:59 am UTC

Aah! After all that, it was simpler than I thought...

dbms_scheduler.run_job(job_name => 'MY_USER.MY_JOB');

No execute/immediate, anything. Just dbms_scheduler.run_job!

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

calling stored procedure,stored function inside the trigger

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs