"I must emphasize that INSTEAD OF triggers operate within the scope of the current transaction. Any change they make to the underlying data is part of the same transaction, and all changes are either entirely committed, or entirely rolled back. This can work to your advantage – take the example of the funds transfer that I mentioned before. This is two INSERTs and either both are committed, or both are rolled back. But there are things that a trigger could do that are not part of the transaction, such as calls to UTL_FILE to write a file outside of the database. These happen even if the transaction is rolled back. Not only that, but there are cases when the code in a trigger might actually be run more than once. If you want to call one of the built-in packages with a name starting with “UTL”, you should probably queue an event to do it, rather than doing it directly."
Send an E-Mail on an Event
So here is a case where you might be tempted to use a trigger: In the HR sample schema that comes with all Oracle databases, there is a table, EMPLOYEES. Each Employee has a foreign key, DEPARTMENT_ID that points to DEPARTMENTS. Each Department has a foreign key, MANAGER_ID, which points to the EMPLOYEES row for the Department Manager. What we want to do is send an e-mail to the department manager whenever a new Employee is added to that Department, or an existing Employee’s DEPARTMENT_ID is changed to point to a new Department.
You say, "Fine, I’ve got a SEND_MAIL procedure that calls the UTL_SMTP built-in package to send e-mails. I’ll just write a trigger to call it when inserting a new EMPLOYEES row with a non-null DEPARTMENT_ID, or updating it with the new DEPARTMENT_ID not equal to the old one."
CREATE OR REPLACE TRIGGER employees_manager_change_email AFTER INSERT OR UPDATE OF department_id ON employees FOR EACH ROW DECLARE manager_first_name employees.first_name%TYPE; manager_last_name employees.last_name%TYPE; manager_email employees.email%TYPE; BEGIN IF :NEW.department_id IS NOT NULL AND (INSERTING OR (UPDATING AND :NEW.department_id <> NVL(:OLD.department_id,0))) THEN SELECT first_name, last_name, email INTO manager_first_name, manager_last_name, manager_email FROM employees WHERE employee_id = (SELECT manager_id FROM departments WHERE department_id = :NEW.department_id); SEND_MAIL (p_from => 'HR', P_to => manager_email, P_subject => 'New Employee in your Department', P_text => 'Please welcome '||:NEW.first_name || ' '||:NEW.last_name || ' to Department '|| TO_CHAR(:NEW.department_id)||' as a '|| :NEW.job_id ||'.'); END IF; END;
Ah, but it won’t work – you’ll get a mutating table exception because you can't select from the EMPLOYEES table at the same time that you are updating it. Some people would then take the offending code and put it in a procedure and make it AUTONOMOUS transaction. That way, since the SELECT from EMPLOYEES is in a separate transaction from the insert or update, it works.
I'm certainly in favor of making this a separate procedure, but making it autonomous is a bad idea. Suppose the change to an Employee's department isn't committed? The user changed his/her mind, and decided not to save the change. The manager would get the e-mail anyway, because the e-mail transaction is no longer dependent on the insert or update transaction. No, we need to make sure that the e-mail only gets sent if the change is committed. Can we make the change raise some sort of notification that an e-mail should be sent, but as part of the current transaction, so it ONLY happens on COMMIT?
So, first, let's make this a procedure to separate the code for sending the e-mail from the trigger code. We are still going to use a trigger to call it because we want it to send the e-mail automatically on the event, but this is not going to be an AUTONOMOUS TRANSACTION, so we can't just call it. We'll need to pass it all the information it needs from the EMPLOYEES row that is being changed by the transaction. Although I'm going to show this as a stand-alone procedure, my usual practice is to put all my procedures and functions in packages.
CREATE OR REPLACE PROCEDURE employee_change_manager_email ( p_department_id IN employees.department_id%TYPE, p_first_name IN employees.first_name%TYPE, p_last_name IN employees.last_name%TYPE, p_job_id IN employees.job_id%TYPE ) IS manager_first_name employees.first_name%TYPE; manager_last_name employees.last_name%TYPE; manager_email employees.email%TYPE; BEGIN SELECT first_name, last_name, email INTO manager_first_name, manager_last_name, manager_email FROM employees WHERE employee_id = (SELECT manager_id FROM departments WHERE department_id = p_department_id); SEND_MAIL (p_from => 'HR', p_to => manager_email, p_subject => 'New Employee in your Department', p_text => 'Please welcome '||p_first_name || ' '||p_last_name || ' to Department '|| TO_CHAR(p_department_id)||' as a '|| p_job_id ||'.'); END employee_change_manager_email;
But how to call it? I can't just call it from the trigger, because it would still raise a mutating table error. If I make it AUTONOMOUS, it gets called whether the transaction is committed or not. So I need to call it in some way that depends on the transaction, but is still in some way autonomous. Fortunately, there are two possibilities in Oracle. We can submit a job with DBMS_JOB, or we can queue it as an event with Oracle Advanced Queuing (AQ). DBMS_JOB is simpler, so I'll save Oracle AQ for another post.
Method 1: DBMS_JOB
We mostly think of the DBMS_JOB package as a way to schedule jobs and have them execute at particular times. And in that role, it has mostly been supplanted by the DBMS_SCHEDULER package. However, it is also possible to use DBMS_JOB to schedule a job to run immediately. And a job runs in its own transaction context, independent of the transaction that scheduled it. Best of all, the DBMS_JOB.SUBMIT procedure requires a COMMIT, which means that if you run it from a trigger, the job does not start running until the triggering transaction is committed. DBMS_SCHEDULER does not need a commit, so it won't do for this task. If the triggering transaction is rolled back, the job will not run. This is exactly what we want for this requirement. So here is our new version of the trigger:
CREATE OR REPLACE TRIGGER employees_manager_change_email AFTER INSERT OR UPDATE OF department_id ON employees FOR EACH ROW DECLARE job_number NUMBER; BEGIN IF :NEW.department_id IS NOT NULL AND (INSERTING OR (UPDATING AND :NEW.department_id <> NVL(:OLD.department_id,0))) THEN DBMS_JOB.SUBMIT (job => job_number, what => 'employee_change_manager_email ( p_department_id => '||:new.department_id||', p_first_name => '''||:new.first_name||''', p_last_name => '''||:new.last_name||''', p_job_id => '''||:new.job_id||''');' ); END IF; END;
This is a little hard to read because the call to the procedure to send the mail is submitted as a string, and we need to concatenate the parameters into the string. The trigger runs DBMS_JOB.SUBMIT to create and submit a job, and the missing "next_date" and "interval" parameters default to running the job immediately and only one time. But the job doesn't actually get submitted until the COMMIT for the INSERT or UPDATE of EMPLOYEES. When the job runs, the procedure runs in a separate transaction, so there is no mutating table.
In another post, I'll give the Oracle AQ method.