Section 19.1.  DML Triggers

19.1. DML Triggers Data Manipulation Language (DML) triggers fire when records are inserted into, updated within, or deleted from a particular table, as shown in Figure 19-1. These are the most common type of triggers, especially for developers; the other trigger types are used primarily by DBAs. There are many options regarding DML triggers. They can fire after or before a DML statement, or they can fire after or before each row is processed within a statement. They can fire for INSERT, UPDATE, or DELETE statements, or combinations of the three. There are also many ways to actually configure DML triggers. To determine what works for your environment, you need to answer the following questions: Should the triggers fire once for the whole DML statement or once per row involved in the statement? Should the triggers fire before or after the whole statement completes or before or after each row is processed? Should the triggers fire for inserts, updates, deletes, or a combination thereof? Figure 19-1. DML triggers fire in response to changes to a database table 19.1.1. DML Trigger Concepts Before diving into the syntax and examples, you may find it useful to review these DML trigger concepts and associated terminology: BEFORE trigger A trigger that executes before a certain operation occurs, such as BEFORE INSERT. AFTER trigger A trigger that executes after a certain operation occurs, such as AFTER UPDATE. Statement-level trigger A trigger that executes for a SQL statement as a whole (which may, in turn, affect one or more individual rows in a database table). Row-level trigger A trigger that executes for a single row that has been affected by the execution of a SQL statement. Suppose that the books table contains 1,000 rows. Then the following UPDATE statement will modify 1,000 rows: UPDATE books SET title = UPPER (title); And if I define a row-level update trigger on the books table, that trigger will fire 1,000 times. NEW pseudo-record A data structure named NEW that looks like and (mostly) acts like a PL/SQL record. This pseudo-record is available only within update and insert DML triggers; it contains the values for the affected row after any changes were made. OLD pseudo-record A data structure named OLD that looks like and (mostly) acts like a PL/SQL record. This pseudo-record is available only within update and delete DML triggers; it contains the values for the affected row before any changes were made. WHEN clause The portion of the DML trigger that is run to determine whether or not the trigger code should be executed (allowing you to avoid unnecessary execution). 19.1.1.1 DML trigger scripts To explore some of the concepts presented in the previous section, we have made the following scripts available on the book's web site: Concept Files Description Statement-level and row-level triggers copy_tables.sql Creates two identical tables, one with data and one empty. statement_vs_row.sql Creates two simple triggers, one statement-level and one row-level.After running these scripts, execute this statement and view the results (with SERVEROUTPUT turned on to watch the activity): INSERT INTO to_table SELECT * FROM from_table; BEFORE and AFTER triggers before_vs_after.sql Creates BEFORE and AFTER triggers. After running the script, execute this statement and view the results: INSERT INTO to_table SELECT * FROM from_table; Triggers for various DML operations one_trigger_per_type.sql Creates AFTER INSERT , UPDATE, and DELETE triggers on to_table. After running the script, execute these commands and view the results: INSERT INTO to_table VALUES (1); UPDATE to_table SET col1 = 10; DELETE to_table; 19.1.1.2 Transaction participation By default, DML triggers participate in the transaction from which they were fired. This means that: If a trigger raises an exception, that part of the transaction will be rolled back. If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction. You cannot issue a COMMIT or ROLLBACK from within a DML trigger. If you define your DML trigger to be an autonomous transaction (discussed in Chapter 14), however, then any DML performed inside the trigger will be saved or rolled backwith your explicit COMMIT or ROLLBACK statementwithout affecting the main transaction. The following sections present the syntax for creating a DML trigger, provide reference information on various elements of the trigger definition, and explore an example that uses the many components and options for these triggers. 19.1.2. Creating a DML Trigger To create (or replace) a DML trigger, use the syntax shown here: 1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER} 3 {INSERT | DELETE | UPDATE | UPDATE OF column list} ON table name 4 [FOR EACH ROW] 5 [WHEN (...)] 6 [DECLARE ... ] 7 BEGIN 8 ... executable statements ... 9 [EXCEPTION ... ] 10 END [trigger name]; The following table provides an explanation of these different elements: Line(s) Description 1 States that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. It is possible, by the way, for a table and a trigger (or procedure and trigger, for that matter) to have the same name. We recommend, however, that you adopt naming conventions to avoid the confusion that will result from this sharing of names. 2 Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed. 3 Specifies the type of DML to which the trigger applies: insert, update, or delete. Note that UPDATE can be specified for the whole record or just for a column list separated by commas. The columns can be combined (separated with an OR) and may be specified in any order. Line 3 also specifies the table to which the trigger is to apply. Remember that each DML trigger can apply to only one table. 4 If FOR EACH ROW is specified, then the trigger will activate for each row processed by a statement. If this clause is missing, the default behavior is to fire only once for the statement (a statement-level trigger). 5 An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger. 6 Optional declaration section for the anonymous block that constitutes the trigger code. If you do not need to declare local variables, you do not need this keyword. Note that you should never try to declare the NEW and OLD pseudo-records. This is done automatically. 7-8 The execution section of the trigger. This is required and must contain at least one statement. 9 Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only. 10 Required END statement for the trigger. You can include the name of the trigger after the END keyword to explicitly document which trigger you are ending. Here are a few examples of DML trigger usage: I want to make sure that whenever an employee is added or changed, all necessary validation is run. Notice that I pass the necessary fields of the NEW pseudo-record to individual check routines in this row-level trigger: CREATE OR REPLACE TRIGGER validate_employee_changes AFTER INSERT OR UPDATE ON employee FOR EACH ROW BEGIN check_age (:NEW.date_of_birth); check_resume (:NEW.resume); END; The following BEFORE INSERT trigger captures audit information for the CEO compensation table. It also relies on the Oracle8i Database autonomous transaction feature to commit this new row without affecting the "outer" or main transaction: CREATE OR REPLACE TRIGGER bef_ins_ceo_comp AFTER INSERT ON ceo_compensation FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO ceo_comp_history VALUES (:NEW.name, :OLD.compensation, :NEW.compensation, 'AFTER INSERT', SYSDATE); COMMIT; END; 19.1.2.1 The WHEN clause Use the WHEN clause to fine-tune the situations under which the body of the trigger code will actually execute. In the following example, I use the WHEN clause to make sure that the trigger code does not execute unless the new salary is changing to a different value: CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE OF salary ON employee FOR EACH ROW WHEN (OLD.salary != NEW.salary) OR (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR (OLD.salary IS NOT NULL AND NEW.salary IS NULL) BEGIN ... In other words, if a user issues an UPDATE to a row and for some reason sets the salary to its current value, the trigger will and must fire, but the reality is that you really don't need any of the PL/SQL code in the body of the trigger to execute. By checking this condition in the WHEN clause, you avoid some of the overhead of starting up the PL/SQL block associated with the trigger. The genwhen.sp file on the book's web site offers a procedure that will generate a WHEN clause to ensure that the new value is actually different from the old. In most cases, you will reference fields in the OLD and NEW pseudo-records in the WHEN clause, as in the example shown above. You may also, however, write code that invokes built-in functions, as in the following WHEN clause that uses SYSDATE to restrict the INSERT trigger to only fire between 9 a.m. and 5 p.m. CREATE OR REPLACE TRIGGER valid_when_clause BEFORE INSERT ON frame FOR EACH ROW WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 ) ... Here are some things to keep in mind when using the WHEN clause: Enclose the entire logical expression inside parentheses. These parentheses are optional in an IF statement, but required in the trigger WHEN clause. Do not include the ":" in front of the OLD and NEW names. This colon (indicating a host variable) is required in the body of the trigger PL/SQL code, but cannot be used in the WHEN clause. You can invoke SQL built-in functions only from within the WHEN clause; you will not be able to call user-defined functions or functions defined in built-in packages (such as DBMS_UTILITY). Attempts to do so will generate an ORA-04076: invalid NEW or OLD specification error. If you need to invoke such functions, move that logic to the beginning of the trigger execution section. The WHEN clause can be used only with row-level triggers. You will get a compilation error (ORA-04077) if you try to use it with statement-level triggers. 19.1.2.2 Working with NEW and OLD pseudo-records Whenever a row-level trigger fires, the PL/SQL runtime engine creates and populates two data structures that function much like records. They are the NEW and OLD pseudo-records ("pseudo" because they don't share all the properties of real PL/SQL records). OLD stores the original values of the record being processed by the trigger; NEW contains the new values. These records have the same structure as a record declared using %ROWTYPE on the table to which the trigger is attached. Here are some rules to keep in mind when working with NEW and OLD: With triggers on INSERT operations, the OLD structure does not contain any data; there is no "old" set of values. FOR EACH ROW Before WHEN In some versions of PL/SQL, the compiler requires that FOR EACH ROW be specified before the WHEN clause because it assumes that a statement trigger is being created unless told otherwise: SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when 2 BEFORE INSERT ON frame 3 WHEN ( new.strike = 'Y' ) 4 FOR EACH ROW 5 BEGIN NULL; END; 8 / ERROR at line 3: ORA-04077: WHEN clause cannot be used with table level triggers SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when 2 BEFORE INSERT ON frame 3 FOR EACH ROW 4 WHEN ( new.strike = 'Y' ) 5 BEGIN NULL; END; 8 / Trigger created. With triggers on UPDATE operations, both the OLD and NEW structures are populated. OLD contains the values prior to the update; NEW contains the values the row will contain after the update is performed. With triggers on DELETE operations, the NEW structure does not contain any data; the record is about to be erased. The NEW and OLD pseudo-records also contain the ROWID pseudo-column; this value is populated in both OLD and NEW with the same value, in all circumstances. Go figure! You cannot change the field values of the OLD structure; attempting to do so will raise the ORA-04085 error. You can modify the field values of the NEW structure. You can't pass a NEW or OLD structure as a "record parameter" to a procedure or function called within the trigger. You can pass only individual fields of the pseudo-record. See the gentrigrec.sp script for a program that will generate code transferring NEW and OLD values to records that can be passed as parameters. When referencing the NEW and OLD structures within the anonymous block for the trigger, you must preface those keywords with a colon, as in: IF :NEW.salary > 10000 THEN... You cannot perform record-level operations with the NEW and OLD structures. For example, the following statement will cause the trigger compilation to fail: BEGIN :new := NULL; END; You can also use the REFERENCING clause to change the names of the pseudo-records within the database trigger; this allows you to write code that is more self-documenting and application-specific. Here is one example: CREATE OR REPLACE TRIGGER audit_update AFTER UPDATE ON frame REFERENCING OLD AS prior_to_cheat NEW AS after_cheat FOR EACH ROW BEGIN INSERT INTO frame_audit (bowler_id, game_id, old_score, new_score) change_date, operation) VALUES (:after_cheat.bowler_id, :after_cheat.game_id, :after_cheat.frame_number, :prior_to_cheat.score, :after_cheat.score, SYSDATE, 'UPDATE'); END; Run the full_old_and_new.sql script to take a look at the behavior of the OLD and NEW pseudo-records. 19.1.2.3 Determining the DML action within a trigger Oracle offers a set of functions (also known as operational directives ) that allow you to determine which DML action caused the firing of the current trigger. Each of these functions returns TRUE or FALSE, as described next: INSERTING Returns TRUE if the trigger was fired by an insert into the table to which the trigger is attached, and FALSE if not. UPDATING Returns TRUE if the trigger was fired by an update of the table to which the trigger is attached, and FALSE if not. DELETING Returns TRUE if the trigger was fired by a delete from the table to which the trigger is attached, and FALSE if not. Using these directives, it's possible to create a single trigger that consolidates the actions required for each different type of operations. Here's one such trigger: /* File on web: one_trigger_does_it_all.sql */ CREATE OR REPLACE TRIGGER three_for_the_price_of_one BEFORE DELETE OR INSERT OR UPDATE ON account_transaction FOR EACH ROW BEGIN -- track who created the new row IF INSERTING THEN :NEW.created_by := USER; :NEW.created_date := SYSDATE; -- track deletion with special audit program ELSIF DELETING THEN audit_deletion(USER,SYSDATE); -- track who last updated the row ELSIF UPDATING THEN :NEW.UPDATED_BY := USER; :NEW.UPDATED_DATE := SYSDATE; END IF; END; The UPDATING function is overloaded with a version that takes a specific column name as an argument. This is handy for isolating specific column updates. /* File on web: overloaded_update.sql */ CREATE OR REPLACE TRIGGER validate_update BEFORE UPDATE ON account_transaction FOR EACH ROW BEGIN IF UPDATING ('ACCOUNT_NO') THEN errpkg.raise('Account number cannot be updated'); END IF; END; Specification of the column name is not case-sensitive. The name is not evaluated until the trigger executes, and if the column does not exist in the table to which the trigger is attached, it will evaluate to FALSE. Operational directives can be called from within any PL/SQL block, not just triggers. They will, however, only evaluate to TRUE within a DML trigger or code called from within a DML trigger. 19.1.3. DML Trigger Example: No Cheating Allowed! One application function for which triggers are perfect is change auditing. Consider the example of Paranoid Pam (or Ms. Trustful as we call her), who runs a bowling alley and has been receiving complaints about people cheating on their scores. She recently implemented a complete Oracle application known as Pam's Bowl-A-Rama Scoring System, and now wants to augment it to catch the cheaters. The focal point of Pam's application is the frame table that records the score of a particular frame of a particular game for a particular player: /* File on web: bowlerama_tables.sql */ CREATE TABLE frame (bowler_id NUMBER, game_id NUMBER, frame_number NUMBER, strike VARCHAR2(1) DEFAULT 'N', spare VARCHAR2(1) DEFAULT 'N', score NUMBER, CONSTRAINT frame_pk PRIMARY KEY (bowler_id, game_id, frame_number)); Pam enhances the frame table with an audit version to catch all before and after values, so that she can compare them and identify fraudulent activity: CREATE TABLE frame_audit (bowler_id NUMBER, game_id NUMBER, frame_number NUMBER, old_strike VARCHAR2(1), new_strike VARCHAR2(1), old_spare VARCHAR2(1), new_spare VARCHAR2(1), old_score NUMBER, new_score NUMBER, change_date DATE, operation VARCHAR2(6)); For every change to the frame table, Pam would like to keep track of before and after images of the affected rows. So she creates the following simple audit trigger: /* File on web: bowlerama_full_audit.sql */ 1 CREATE OR REPLACE TRIGGER audit_frames 2 AFTER INSERT OR UPDATE OR DELETE ON frame 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 INSERT INTO frame_audit(bowler_id,game_id,frame_number, 7 new_strike,new_spare,new_score, 8 change_date,operation) 9 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number, 10 :NEW.strike,:NEW.spare,:NEW.score, 11 SYSDATE,'INSERT'); 12   13 ELSIF UPDATING THEN 14 INSERT INTO frame_audit(bowler_id,game_id,frame_number, 15 old_strike,new_strike, 16 old_spare,new_spare, 17 old_score,new_score, 18 change_date,operation) 19 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number, 20 :OLD.strike,:NEW.strike, 21 :OLD.spare,:NEW.spare, 22 :OLD.score,:NEW.score, 23 SYSDATE,'UPDATE'); 24   25 ELSIF DELETING THEN 26 INSERT INTO frame_audit(bowler_id,game_id,frame_number, 27 old_strike,old_spare,old_score, 28 change_date,operation) 29 VALUES(:OLD.bowler_id,:OLD.game_id,:OLD.frame_number, 30 :OLD.strike,:OLD.spare,:OLD.score, 31 SYSDATE,'DELETE'); 32 END IF; 33 END audit_frames; Notice that for the INSERTING clause (lines 6-11), she relies on the NEW pseudo-record to populate the audit row. For UPDATING (lines 14-23), a combination of NEW and OLD information is used. For DELETING (lines 26-31), Pam only has OLD information with which to work. With this trigger in place, Pam can sit back and wait for action. Of course, Pam doesn't announce her new auditing system. In particular, Sally Johnson (a very ambitious but not terribly skilled bowler) has no idea she is being watched. Sally has decided that she really wants to be the champion this year, and will stop at nothing to make it happen. Her father owns the bowling alley, she has access to SQL*Plus, and she knows that her bowler ID is 1. All that constitutes enough privilege and information to allow her to bypass the application GUI altogether, connect directly into SQL*Plus, and work some very unprincipled "magic." Sally starts out by giving herself a strike in the first frame: SQL> INSERT INTO frame 2 (BOWLER_ID,GAME_ID,FRAME_NUMBER,STRIKE) 3 VALUES(1,1,1,'Y'); 1 row created. But then she decides to be clever. She immediately downgrades her first frame to a spare to be less conspicuous: SQL> UPDATE frame 2 SET strike = 'N', 3 spare = 'Y' 4 WHERE bowler_id = 1 5 AND game_id = 1 6 AND frame_number = 1; 1 row updated. Uh oh! Sally hears a noise in the corridor. She loses her nerve and tries to cover her tracks: SQL> DELETE frame 2 WHERE bowler_id = 1 3 AND game_id = 1 4 AND frame_number = 1; 1 row deleted. SQL> COMMIT; Commit complete. She even verifies that her entries were deleted: SQL> SELECT * FROM frame; no rows selected Wiping the sweat from her brow, Sally signs out, but vows to come back later and follow through on her plans. Ever suspicious, Pam signs in and quickly discovers what Sally was up to by querying the audit table (Pam might also consider setting up an hourly job via DBMS_JOB to automate this part of the auditing procedure): SELECT bowler_id, game_id, frame_number, old_strike, new_strike, old_spare, new_spare, change_date, operation FROM frame_audit; Here is the output: BOWLER_ID GAME_ID FRAME_NUMBER O N O N CHANGE_DA OPERAT --------- ------- ------------ - - - - --------- ------ 1 1 1 Y N 12-SEP-00 INSERT 1 1 1 Y N N Y 12-SEP-00 UPDATE 1 1 1 N N 12-SEP-00 DELETE Sally is so busted! The audit entries show what Sally was up to even though no changes remain behind in the frame table. All three statements were audited by Pam's DML trigger: the initial insert of a strike entry, the downgrade to a spare, and the subsequent removal of the record. 19.1.3.1 Applying the WHEN clause After using her auditing system for many successful months, Pam undertakes an effort to further isolate potential problems. She reviews her application front end and determines that the strike, spare, and score fields are the only ones that can be changed. Thus her trigger can be more specific: CREATE OR REPLACE TRIGGER audit_update AFTER UPDATE OF strike, spare, score ON frame REFERENCING OLD AS prior_to_cheat NEW AS after_cheat FOR EACH ROW BEGIN INSERT INTO frame_audit (...) VALUES (...); END; After a few weeks of this implementation, Pam is still not happy with the auditing situation because audit entries are being created even when values are set equal to themselves. Updates like this one are producing useless audit records that show nothing changing: SQL> UPDATE FRAME 2 SET strike = strike; 1 row updated. SQL> SELECT old_strike, 2 new_strike, 3 old_spare, 4 new_spare, 5 old_score, 6 new_score 7 FROM frame_audit; O N O N OLD_SCORE NEW_SCORE - - - - ---------- ---------- Y Y N N Pam needs to further isolate the trigger so that it fires only when values actually change. She does this using the WHEN clause shown here: /* File on web: final_audit.sql */ CREATE OR REPLACE TRIGGER audit_update AFTER UPDATE OF STRIKE, SPARE, SCORE ON FRAME REFERENCING OLD AS prior_to_cheat NEW AS after_cheat FOR EACH ROW WHEN ( prior_to_cheat.strike != after_cheat.strike OR prior_to_cheat.spare != after_cheat.spare OR prior_to_cheat.score != after_cheat.score ) BEGIN INSERT INTO FRAME_AUDIT ( ... ) VALUES ( ... ); END; Now entries will appear in the audit table only if something did indeed change, allowing Pam to quickly identify possible cheaters. Pam performs a quick final test of her trigger. SQL> UPDATE frame 2 SET strike = strike; 1 row updated. SQL> SELECT old_strike, 2 new_strike, 3 old_spare, 4 new_spare, 5 old_score, 6 new_score 7 FROM frame_audit; no rows selected 19.1.3.2 Using pseudo-records to fine-tune trigger execution Pam has implemented an acceptable level of auditing in her system; now she'd like to make it a little more user-friendly. Her most obvious idea is to have her system add 10 to the score for frames recording a strike or spare. This allows the scoreperson to track only the score for subsequent bowls while the system adds the strike score. CREATE OR REPLACE TRIGGER set_score BEFORE INSERT ON frame FOR EACH ROW WHEN ( NEW.score IS NULL ) BEGIN IF :NEW.strike = 'Y' OR :NEW.spare = 'Y' THEN :NEW.score := :NEW.score + 10; END IF; END; Remember that field values in the NEW records can be changed only in BEFORE row triggers. Being a stickler for rules, Pam decides to add score validation to her set of triggers: /* File on web: validate_score.sql */ CREATE OR REPLACE TRIGGER validate_score AFTER INSERT OR UPDATE ON frame FOR EACH ROW BEGIN IF :NEW.strike = 'Y' AND :NEW.score < 10 THEN RAISE_APPLICATION_ERROR ( -20001, 'ERROR: Score For Strike Must Be >= 10' ); ELSIF :NEW.spare = 'Y' AND :NEW.score < 10 THEN RAISE_APPLICATION_ERROR ( -20001, 'ERROR: Score For Spare Must Be >= 10' ); ELSIF :NEW.strike = 'Y' AND :NEW.spare = 'Y' THEN RAISE_APPLICATION_ERROR ( -20001, 'ERROR: Cannot Enter Spare And Strike' ); END IF; END; Now when there is any attempt to insert a row that violates this condition, it will be rejected: SQL> INSERT INTO frame VALUES(1,1,1,'Y',NULL,5); INSERT INTO frame * ERROR at line 1: ORA-20001: ERROR: Score For Strike Must >= 10 19.1.4. 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_commentary BEFORE INSERT ON golf_scores FOR EACH ROW BEGIN 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_par BEFORE INSERT ON golf_scores FOR EACH ROW WHEN (NEW.score < 72) BEGIN :NEW.commentary := 'Under Par'; END; CREATE OR REPLACE TRIGGER golf_commentary_par BEFORE INSERT ON golf_scores FOR EACH ROW WHEN (NEW.score = 72) BEGIN :NEW.commentary := 'Par'; END; CREATE OR REPLACE TRIGGER golf_commentary_over_par BEFORE INSERT ON golf_scores FOR EACH ROW WHEN (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_one BEFORE INSERT ON incremented_values FOR EACH ROW BEGIN :NEW.value_incremented := :NEW.value_incremented + 1; END; / CREATE OR REPLACE TRIGGER increment_by_two BEFORE INSERT ON incremented_values FOR EACH ROW BEGIN IF :NEW.value_incremented > 1 THEN :NEW.value_incremented := :NEW.value_incremented + 2; END IF; END; / INSERT INTO incremented_values VALUES(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 IDbut even those theories should not be relied upon. 19.1.5. Mutating Table Errors 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 his 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 ROW DECLARE 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 applies only to row-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. 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. Because each release of Oracle renders mutating tables less and less of a problem, it's not really necessary to perform a full demonstration here. However a demonstration script named mutation_zone.sql is available on the book's web site. In addition, the file mutating_template.sql offers a package that can serve as a template for creating your own package to defer processing of row-level logic to the statement level.