TRIGGER for calling a stored procedure

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
begin
  if :new.flag = 'Process' then
    your_procedure;
  end if;
end;

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).

Case:
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

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.

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)

@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.

@mustaccio - nice demo :) - got confused where the trigger is scheduling a job... In that case the user would not have to wait, right?

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.