Multiple Triggers of the Same Type

Above and beyond all of the options presented for DML triggers, it is also possible to have multiple triggers of the same type attached to a single table. Switching from bowling to golf, consider the following example that provides a simple commentary of a golf score by determining its relationship to a par score of 72.

A single row-level BEFORE INSERT trigger would suffice:

/* File on web: golf_commentary.sql */CREATE OR REPLACE TRIGGER golf_commentaryBEFORE INSERT ON golf_scoresFOR EACH ROWBEGIN IF :NEW.score < 72 THEN :NEW.commentary := 'Under Par'; ELSIF :NEW.score = 72 THEN :NEW.commentary := 'Par'; ELSE :NEW.commentary := 'Over Par'; END IF;END;

However, the requirement could also be satisfied with three separate row-level BEFORE INSERT triggers with mutually exclusive WHEN clauses:

CREATE OR REPLACE TRIGGER golf_commentary_under_parBEFORE INSERT ON golf_scoresFOR EACH ROWWHEN (NEW.score < 72)BEGIN :NEW.commentary := 'Under Par';END; CREATE OR REPLACE TRIGGER golf_commentary_parBEFORE INSERT ON golf_scoresFOR EACH ROWWHEN (NEW.score = 72)BEGIN :NEW.commentary := 'Par';END; CREATE OR REPLACE TRIGGER golf_commentary_over_parBEFORE INSERT ON golf_scoresFOR EACH ROWWHEN (NEW.score > 72)BEGIN :NEW.commentary := 'Over Par';END;

Both implementations are perfectly acceptable and have advantages and disadvantages. A single trigger is easier to maintain because all of the code is in one place, while separate triggers reduce parse and execution time when more complex processing is required.

One pitfall of multiple triggers is that there is no guarantee of the order in which they will fire. While this is not a concern in the above example, it could be a problem in others, as shown next.

What values will be shown by the final query?

/* File on web: multiple_trigger_seq.sql */DROP TABLE incremented_values; CREATE TABLE incremented_values(value_inserted NUMBER, value_incremented NUMBER); CREATE OR REPLACE TRIGGER increment_by_oneBEFORE INSERT ON incremented_valuesFOR EACH ROWBEGIN :NEW.value_incremented := :NEW.value_incremented + 1;END;/ CREATE OR REPLACE TRIGGER increment_by_twoBEFORE INSERT ON incremented_valuesFOR EACH ROWBEGIN IF :NEW.value_incremented > 1 THEN :NEW.value_incremented := :NEW.value_incremented + 2; END IF;END;/ INSERT INTO incremented_valuesVALUES(1,1); SELECT * FROM incremented_values;

Any guesses? On my database I got this result:

SQL> SELECT * 2 FROM incremented_values; VALUE_INSERTED VALUE_INCREMENTED-------------- ----------------- 1 2

So the increment_by_two trigger fired first and did nothing because the value_incremented column was not greater than 1; then the increment_by_one trigger fired to increase the value_incremented column by 1. Is this the result you will receive? There is no guarantee. Will this result always be received? Again, there is no guarantee. Oracle explicitly states that there is no way to control or assure the order in which multiple triggers of the same type on a single table will fire. There are many theories, the most prevalent being that triggers fire in reverse order of creation or by order of object ID—but even those theories should not be relied upon.

18.1.5 Mutating Table Errors: Problem and Solution

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-4091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Suppose, for example, that I want to put a special check on my employee table to make sure that when a person is given a raise, that person's new salary is not more than 20% above the next-highest salary in their department.

I would therefore like to write a trigger like this:

CREATE OR REPLACE TRIGGER brake_on_raises BEFORE UPDATE OF salary ON employee FOR EACH ROWDECLARE l_curr_max NUMBER;BEGIN SELECT MAX (salary) INTO l_curr_max FROM employee; IF l_curr_max * 1.20 < :NEW.salary THEN errpkg.RAISE ( employee_rules.en_salary_increase_too_large, :NEW.employee_id, :NEW.salary ); END IF;END;

But when I try to perform an update that, say, doubles the salary of the PL/SQL programmer (yours truly), I get this error:

ORA-04091: table SCOTT.EMPLOYEE is mutating, trigger/function may not see it

Here are some guidelines to keep in mind regarding mutating table errors:

· In general, a row-level trigger may not read or write the table from which it has been fired. The restriction only applies torow-level triggers, however. Statement-level triggers are free to both read and modify the triggering table; this fact gives us a way to avoid the mutating table error, as discussed in Section 18.1.5.2.

· If you make your trigger an autonomous transaction (by adding the PRAGMA AUTONOMOUS TRANSACTION statement and committing inside the body of the trigger), then you will be able to query the contents of the firing table. However, you will still not be allowed to modify the contents of the table.