Avatar of gotetioracle
 asked on

TRIGGER for calling a stored procedure


I have table called data_process in this table there are there flags
1) Process 2) complete 3) error
If the user presses the process button then flag in the table updates to 'process' and a oracle job is called for every 30 min which calls a procedure and this procedure process the data and updates flag status as complete or error

The solution i am searching for is i want to trigger this procedure when ever user press the process button and i don't want to call it at application level i want to do this task at backend at database level without impacting the performance of database.

Note : data size is in TB and there are multiple users at multiple locations and does this task at a time.

Somebody help me

Oracle DatabaseVMware

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
Gerwin Jansen

Hi, changing this will result in a different performance because you now have batch processing in place (every 30 mins) and when changing to a trigger setup, the procecure will be called every time a record is updated in your data_process table to 'Process'.

You can solve this by creating an AFTER UPDATE trigger that is on your data_process table. The trigger checks whether the update is of type 'Process' and if so, it calls your procedure. Working with a trigger like is is a better solution imho because your database load will get spread over the hour instead of peaks every 30 mins.

This is the basic idea:

create or replace trigger trg_upd_data_process
after update on data_process
for each row
  if :new.flag = 'Process' then
  end if;

Try/test this on a development env, don't forget to disable your 30 min job as you don't need it for this case anymore.

Thanks you very much for  your solution  @gerwinjansen i will implement in DEvelopment enviornment.But I have one query for suppose there are four users using my application (u1,u2,u3,u4).

If u1 has press button 'process' and our triggered is fired and procedure is called and procedure is doing its defined activity.Now u2 has pressed 'process' button and again trigger is fired.similarly u3 an u4 has pressed process button.and data is large.

My Query:
 Will my performance of the database is degraded
Gerwin Jansen

You would have to do some testing in your development environment to be sure. But when users are pressing the button now, processing has to wait for max. 30 minutes to take place. Then all 4 'jobs' get processed, depeding on your procedure, probably sequentially and finish after all jobs are processed. When using the trigger, the procedure will be started in parallel when 2 user press the process button simultaneously, the procedure for the user with 'data is large' will run longer, the other instance will be finished earlier. You should do some monitoring on your database server as you are testing and log how long processing takes place. Best is to simulate your production 'load' on your dev. server and extrapolate from that what it means for prod.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Using a trigger to call a the procedure is not what I should consider executing at 'backend' : if the procedure takes for example 20 seconds , your user probably has to wait 20 seconds before he can take the next action in your application.
See oracle documentation for dbms_job :  you can use the trigger to schedule a job in background execution an that job can execute the procedure.

Processing as single jobs instead of a batch will spread your load but increase the load if
1>  there is much overhead to do for a single job that now is done only once in 30 minutes
2>  if in the current situation one  'data proces'-record can be changed to "Process" by more users  there will be a grow in load  :
example if in the current situation  within 30 minutes 20 user hit the button to proces data x1 and that results in one processing of data x1 then it will be processed till 20 times if you schedule the job immediate,
if in the current situation there are 20 records involved (data x1 per user) the load will be the same (except for the overhead and the overhead of the job-schedular)
Gerwin Jansen

@flow01 : "your user probably has to wait 20 seconds before he can take the next action in your application" -> the trigger is executed by the database after an update is done on the table. The user does not have to wait for this. Trigger handling is done by the database itself, there is no much difference if you use the trigger to schedule the (existing) dbms_job. We could discuss further about load increasing or not, best is testing because we don't know what is being processed exactly or how the procedure looks.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gerwin Jansen

@mustaccio - nice demo :) - got confused where the trigger is scheduling a job... In that case the user would not have to wait, right?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Certainly, it won't take long to schedule a job, so the wait will be short, few milliseconds may be, but it definitely won't depend on the time it might take to run that job.
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.