Mutating Table Errors

Mutating Table Problems with DELETE CASCADE

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The restrictions on such a table apply only to the session that issued the statement in progress. For all row triggers, that were fired as the result of a DELETE CASCADE, there are two important restrictions regarding mutating tables. These restrictions prevent a trigger from seeing an inconsistent set of data. The SQL statements of a trigger cannot read from (query) or modifya mutating table of the triggering statement. Example 1 Select in a mutating table from a row trigger (Tested on Oracle 8.1.7) We want to explain this situation on an example. We have two tables "A" and "B". "A" is the master table and "B" the detail table. We specified a foreign key between "B" and "A" with the CASCADE DELETE option. Here are the CREATE statements drop table B; drop table A; create table A (   ida    number   not null,   vala   varchar2(10),   primary key(ida)); create table B (   idb    number,   valb   varchar2(10),   foreign key (idb) references A (ida) on delete cascade) / create or replace trigger b_br after delete on B for each row declare     n integer; begin   select count(*) into n from A;   dbms_output.put_line('there are ' || n || ' rows in A');   dbms_output.put_line('after statment on B');   dbms_output.new_line; end; / insert into A values(1,'Table A'); insert into A values(2,'Table A'); insert into B values(1,'Table B'); insert into B values(1,'Table B'); commit; set serveroutput on; delete from A where idA = 1; ERROR at line 1: ORA-04091: table SCOTT.A is mutating, trigger/function may not see ORA-06512: at "SCOTT.B_BR", line 4 ORA-04088: error during execution of trigger 'SCOTT.B_BR' Notice that the SQL statement ( "select count(*) into n from A" ) is run for the first row of the table, and then the AFTER row trigger B_BR is fired. In turn, a statement in the AFTER row trigger body attempts to query the original table A. However, because the table A is mutating due to the CASCADE DELETE foreign key, this query is not allowed by Oracle. If attempted, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. Solution: Use statement trigger instead of row trigger If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutatingwhen the trigger fires, and the trigger does output the correct data. SQL> delete from A where idA = 1; there are 1 rows in A after statment on B 1 row deleted. SQL> select count(*) from B;    COUNT(*) ----------          0 It is not always possible to change the row trigger to a statement trigger. In this case, the oracle manual proposes the following solution: Example 2 Cascading Update with Oracle 8i Before Oracle8i, there was a "constraining error" that prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint. As of Oracle8i, there is no constraining error. In addition, checking of the foreign key is deferred until at least the end of the parent statement. The mutating error still prevents the trigger from reading or modifying the table that the parent statement is modifying. This allows most foreign key constraint actions to be implemented via their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. However there is one problem with a multirow update - let's demonstrate this on an example. create table P (   p1 number not null,   primary key (p1)); create table F (   f1 number,   foreign key (f1) references P (p1) on delete cascade); create trigger pu_ar after update on p for each row begin   if (:new.p1 != :old.p1) then     update f        set f1 = :new.p1      where f1 = :old.p1;   end if; end; / insert into p values (1); insert into p values (2); insert into p values (3); insert into f values (1); insert into f values (2); insert into f values (3); commit; update p set p1 = p1+1; commit; SQL> select * from p;         P1 ----------          2  <===== OK          3  <===== OK          4  <===== OK SQL> select * from f;         F1 ----------          4   <===== Wrong !          4   <===== Wrong !          4   <===== Wrong ! The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost. To avoid this problem, you must forbid to update to an existing primary key, if this primary key have any childs, this can be accomplished with the following trigger. create or replace trigger pu_ar after update on P for each row declare   echildexists   exception;   -- check if child table have child records with this new   -- key, this is not allowed.   cursor curs_exists is   select 'x'     from F    where f1 = :new.p1;   rtf  curs_exists%rowtype; begin   if(:new.p1 <> :old.p1) then     open curs_exists;     fetch curs_exists into rtf;     if (curs_exists%found) then       close curs_exists;       raise echildexists;     end if;     close curs_exists;     update f        set f1 = :new.p1      where f1 = :old.p1;   end if; exception   when echildexists then     raise_application_error (     -20102,'error: this primary key: '     ||to_char(:new.p1)     ||' exists and has child rows in f, this triggers     again an update and so on ...'); end pu_ar; / Test the update SQL> update p set p1 = p1+1;   ERROR at line 1: ORA-20102: error: this primary key: 2 exists and has child rows in f, this triggers again an update and so on ... ORA-06512: at "SCOTT.PU_AR", line 34 ORA-04088: error during execution of trigger 'SCOTT.PU_AR' Example 3 Using a temporary table If you need to update a mutating table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers - an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table. In the next example, "from the real world", we want to show this. The table CUG can only have records of the following types o    A: Type = 1 o    B: Type = 2 (Leader for C or D) o    C: Type = 3 (Lead by B) o    D: Type = 4 (Lead by B) Note, that the types C and D must be leaded by the type B.  Create table CUG drop table CUG cascade constraints; create table CUG (   id_cug      number(12) not null primary key,   id_B        number(12) not null,   type        number(1), foreign key (id_B) references CUG (id_cug)  on delete cascade); Next we create a temporary table to avoid the "Mutating Table Problem". drop table CUGTMP; create global temporary table CUGTMP (   id_B        number(12),   type        number(1)) on commit delete rows; The following trigger checks new rows (Inserts) in CUG create or replace trigger bi_r before insert on CUG for each row declare   l_type     CUG.type%type; begin   if (:new.type in (3,4)) then     select type into l_type from CUG      where id_cug = :new.id_B;   end if;   if (l_type != 2) then      raise_application_error(-20002,      'C and D CUGs must have a leading B');   end if; end; / The following Trigger saves the new values for id_B in the temporary table. create or replace trigger au_r after update of id_B on CUG for each row begin   insert into CUGTMP (id_B,type)   values (:new.id_B,:new.type); end; / The following Trigger finally checks, that C and D CUGs belong to a B CUG. create or replace trigger au_s after update of id_B on CUG declare   l_id_B        number(12);   l_typeCD      number(1);   l_typeB       number(1);   cursor cur_cugtmp is   select id_B,type    from CUGTMP; begin   open cur_cugtmp;   loop     fetch cur_cugtmp into l_id_B,l_typeCD;     exit when cur_cugtmp%notfound;      select type into l_typeB from CUG       where id_cug = l_id_B;     if (l_typeB != 2) then        raise_application_error(-20002,        'C and D CUGs must have a leading B');     end if;   end loop;   close cur_cugtmp; end; / Test insert and update insert into CUG (id_cug,id_B,type)   values (0,0,0); insert into CUG (id_cug,id_B,type)   values (1,0,2); insert into CUG (id_cug,id_B,type)   values (2,0,2); insert into CUG (id_cug,id_B,type)   values (3,1,3); insert into CUG (id_cug,id_B,type)   values (4,2,3); insert into CUG (id_cug,id_B,type)   values (5,1,4); insert into CUG (id_cug,id_B,type)   values (6,2,4); commit; SQL> select * from CUG;     ID_CUG       ID_B       TYPE ---------- ---------- ----------          0          0          0          1          0          2          2          0          2          3          1          3          4          2          3          5          1          4          6          2          4 Now, we want that that the CUGs 3,4,5,6 changes the leadership to CUG 2 SQL> update CUG set id_B = 2 where id_cug in (3,4,5,6); 4 rows updated. SQL> select * from cug;     ID_CUG       ID_B       TYPE ---------- ---------- ----------          0          0          0          1          0          2          2          0          2          3          2          3          4          2          3          5          2          4          6          2          4 Next we delete the "Leader" with ID_CUG = 2. All childs must be deleted automatically with the DELETE CASCADE. SQL> select * from cug;     ID_CUG       ID_B       TYPE ---------- ---------- ----------          0          0          0          1          0          2 Everything looks fine now.