The dwindling mutation zone

As Oracle matures, it is getting better at handling the mutating table situation. As a matter of fact, starting with Oracle 8.1.5, Kay's original code would work fine. This is because the foreign key check is now done after the row-level triggers fire. One situation that remains vulnerable to painful mutation is when a foreign key specifies the ON DELETE CASCADE clause.

Consider this rudimentary example of a simple master table and detail table, with a foreign key constraint between them that includes the ON DELETE CASCADE clause:

CREATE TABLE master_table(master_id NUMBER NOT NULL PRIMARY KEY); CREATE TABLE detail_table(detail_id NUMBER NOT NULL, master_id NUMBER NOT NULL, CONSTRAINT detail_to_emp FOREIGN KEY (master_id) REFERENCES master_table (master_id) ON DELETE CASCADE); CREATE OR REPLACE TRIGGER after_delete_masterAFTER DELETE ON master_tableFOR EACH ROWDECLARE CURSOR curs_count_detail IS SELECT COUNT(*) FROM detail_table; v_detail_count NUMBER;BEGIN OPEN curs_count_detail; FETCH curs_count_detail INTO v_detail_count; CLOSE curs_count_detail;END;

Delete operations on the master_table cause associated detail_table records to disappear; then the trigger attempts to count the rows in detail_table. Oracle cannot guarantee an accurate result because it is unsure of how many rows remain. Thus, it is left with no choice but to loudly announce the potential mutation:

SQL> DELETE master_table;*ERROR at line 1:ORA-04091: table SCOTT.DETAIL_TABLE is mutating, trigger/function may not see itORA-06512: at "SCOTT.AFTER_DELETE_MASTER", line 3ORA-06512: at "SCOTT.AFTER_DELETE_MASTER", line 7ORA-04088: error during execution of trigger 'SCOTT.AFTER_DELETE_MASTER'

Even this situation can be avoided usingautonomous transactions as introduced in Oracle 8.1. This means that the trigger will fire as its own transaction in order to avoid the possibility of mutation.

CREATE OR REPLACE TRIGGER after_delete_masterAFTER DELETE ON master_tableFOR EACH ROWDECLARE -- declare as autonomous PRAGMA AUTONOMOUS_TRANSACTION; CURSOR curs_count_detail IS SELECT COUNT(*) FROM detail_table; v_detail_count NUMBER;BEGIN OPEN curs_count_detail; FETCH curs_count_detail INTO v_detail_count; DBMS_OUTPUT.PUT_LINE('detail count = ' || v_detail_count); CLOSE curs_count_detail;END;

Now the trigger can execute because it is not a part of the actual delete transaction.

SQL> DELETE master_table;detail count = 1 1 row deleted.

As you can see, the mutation zone is becoming less and less of a problem.

18.2 DDL Triggers

Oracle8 introduced a long-awaited feature: the firing of triggers when Data Definition Language (DDL) statements are executed. Simply put, DDL is any SQL statement used to create or modify a database object such as a table or an index. Here are some examples of DDL statements:

· CREATE TABLE / ALTER TABLE

· CREATE INDEX

· CREATE TRIGGER / DROP TRIGGER

Each of these statements results in the creation, alteration, or removal of a database object.

The syntax for creating these triggers is remarkably similar to that of DML triggers, except that the firing events differ and they are not applied to tables.