Рис.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, Мутирование таблиц
Автор: Alhymik