Oracle, bypassing mutable tables

Fig.1 - artists mutants ninja turtles

The mutation of tables (error ORA-04091) occurs if a change in the read level of the table is performed or the data is read from the same table for which this trigger should have been triggered.

Consider two ways to circumvent this situation. The first one — through the package — is as ancient as the blow of a mawashi geri with a reversal, it looks spectacular, but it takes a long time to prepare and is complex in execution. The second is fresher and simpler using compound triggers.

create table turtles as select '' name, '' essence from dual union all select '', '' from dual union all select '', '' from dual union all select '', '' from dual union all select '', '' from dual; 

NAME	ESSENCE
Splinter	Rat
Leonardo	Painter
Raphael	Painter
Michelangelo	Painter
Donatello	Painter

We agree that when the Spliter from a rat mutates into a sensei, the artists will have to automatically turn into a ninja. It would seem that for this should come up a trigger

 create or replace trigger tr_turtles_bue before update of essence on turtles for each row when ( new.name = '' and old.essence = '' and new.essence = '' ) begin update turtles set essence = '' where essence = ''; end; 

But when I try to update the record

 update turtles set essence = '' where name = '' 

arises
ORA-04091: table SCOTT.TURTLES is mutating
')
Remove this trigger

 drop trigger tr_turtles_bue; 

Workaround 1 . Using the package and trigger level instructions.

 create or replace package pkg_around_mutation is bUpdPainters boolean; procedure update_painters; end pkg_around_mutation; / create or replace package body pkg_around_mutation is procedure update_painters is begin if bUpdPainters then bUpdPainters := false; update turtles set essence = '' where essence = ''; end if; end; end pkg_around_mutation; / create or replace trigger tr_turtles_bue before update of essence on turtles for each row when ( new.name = '' and old.essence = '' and new.essence = '' ) begin pkg_around_mutation.bUpdPainters := true; end tr_turtles_bue; / create or replace trigger tr_turtles_bu after update on turtles begin pkg_around_mutation.update_painters; end tr_turtles_bu; / 

Workaround 2 . Using compound trigger (compound DML triggers).
Available starting with Oracle 11g.

 create or replace trigger tr_turtles_ue for update of essence on turtles compound trigger bUpdPainters boolean; before each row is begin if :new.name = '' and :old.essence = '' and :new.essence = '' then bUpdPainters := true; end if; end before each row; after statement is begin if bUpdPainters then update Turtles set essence = '' where essence = ''; end if; end after statement; end tr_turtles_ue; 

We try

 update turtles set essence = '' where name = '' 

NAME	ESSENCE
Splinter	Sensei
Leonardo	Ninja
Raphael	Ninja
Michelangelo	Ninja
Donatello	Ninja

Even if you are faced with a more complicated case of mutation, you can use the cited bypass idea. First of all, it is connected with the fact that there is no mutation in the trigger level of the instruction, unlike the trigger of the string level. Secondly, you can use either variables (signs, latches, PL PL tables) in a specially created auxiliary package, or variables that are global for all sections of the composite trigger, which is more preferable, starting with Oracle 11g.

So now you know kung fu. Until new meetings.

Additional materials: Compound DML Triggers , Table Mute