Oracle, обход мутирующих таблиц

Рис.1 – художники мутанты ниндзя черепашки

Мутирование таблиц (ошибка ORA-04091) возникает, если в триггере уровня строки выполняется изменение или чтение данных из той же самой таблицы, для которой данный триггер должен был сработать.

Рассмотрим два способа обхода данной ситуации. Первый – через пакет – древний, как удар маваши гери с разворота, смотрится эффектно, но долго готовится и сложен в исполнении. Второй – более свежий и простой – с использованием составных триггеров.

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
Сплинтер	Крыса
Леонардо	Художник
Рафаэль	Художник
Микеланджело	Художник
Донателло	Художник

Условимся, что когда Сплитер из крысы мутируют в сэнсэя, художники должны будут автоматически превратиться в ниндзя. Казалось бы, для этого должен подойти такой триггер

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; 

Но при попытке обновить запись

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

возникает
ORA-04091: table SCOTT.TURTLES is mutating, trigger/function may not see it

Удалим этот триггер

drop trigger tr_turtles_bue;

Способ обхода 1. С помощью пакета и триггера уровня инструкции.

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;
/ 

Способ обхода 2. С помощью составного триггера (compound DML triggers).
Доступно, начиная с 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; 

Пробуем

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

NAME	ESSENCE
Сплинтер	Сэнсэй
Леонардо	Ниндзя
Рафаэль	Ниндзя
Микеланджело	Ниндзя
Донателло	Ниндзя

Даже если вы столкнулись с более сложным случаем мутации, можно использовать приведенную идею обхода. Она, во-первых, связана с тем, что в триггере уровня инструкции, в отличие от триггера уровня строки, мутации не возникает. Во-вторых, можно использовать либо переменные (признаки, защелки, таблицы PL SQL) в специально созданном вспомогательном пакете, либо переменные, глобальные для всех секций составного триггера, что более предпочтительно, начиная с версии Oracle 11g.

Так что теперь и вы знаете кунг-фу. До новых встреч.

Дополнительные материалы: Compound DML Triggers, Мутирование таблиц