Remember your dreams

Remember your dreams
Remember your dreams

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;


/

Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...