Tuesday, April 13, 2010

Using DMBS_JOB in a trigger

How to call DBMS_JOB from within a trigger.


I ran into a case where I needed to perform multiple DML’s from within a trigger. This proved to be unacceptable since to do this made the original insert/update unreasonably slow. The solution was to write a procedure to do the related insert/updates and call the procedure from within the trigger using dbms_job.


My extreme case was where I needed to do 131 insert/updates to an aggregate table for every insert/update to the data table. Using a cursor and looping through to do the inserts was taking over a minute and a half. Using dbms_job to perform the same feat took about a second and a half. The insert/updates on the aggregate table is much faster (does not take the original minute and a half). Calling DBMS_JOB.SUBMIT kicks off multiple simultaneous sessions (inserts/updates).


Example of a Trigger using dbms_job to call a procedure.


CREATE OR REPLACE TRIGGER HR.AR$EMPLOYEES
AFTER INSERT OR UPDATE OF EMP_NAME
ON HR.EMPLOYEES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_id number;
l_job number;

BEGIN
v_id := :NEW.EMP_ID;
dbms_job.submit(l_job,'HR.POPULATION('||v_id||');');
END;
/


Example of a Trigger using a cursor to query and results and call dbms_job based on the results.



CREATE OR REPLACE TRIGGER HR.AUR$EMPLOYEES
AFTER UPDATE OF EMP_NAME
ON HR.EMPLOYEES
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_id number;
l_job number;
BEGIN
FOR content_rec IN (
select emp_id
from HR.JOBS
where EMP_NAME = :NEW.EMP_NAME)
LOOP
v_id := content_rec.emp_id;
dbms_job.submit(l_job,'HR.POPULATION('||v_id||');');
END LOOP;

END;


/

1 comment:

  1. Thanks for the tip. I was looking for something like this.

    ReplyDelete

Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.

How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...