We help IT Professionals succeed at work.

TRIGGER for calling a stored procedure

gotetioracle
gotetioracle asked
on
663 Views
Last Modified: 2012-08-13
Hi,

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

Regards,
GSK
Comment
Watch Question

Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
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.

Author

Commented:
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
Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
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.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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 JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
@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.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016

Commented:
@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.
Gerwin JansenTopic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.