"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?
The Procedure
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.
