Рис.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, Мутирование таблиц
Поделиться с друзьями
Комментарии (5)
Pazus
24.07.2016 20:14+1Хочу предупредить аудиторию, что использование составных триггеров связано с документированным багом СУБД.
Представим, что в составном триггере объявлена коллекция, которая заполняется в частях each row и обрабатывается в afer statement. Если вызвать dml-операцию удаления, по таблице, триггер отработает корректно. Если же удалить строку из ДРУГОЙ таблицы, на которую наша таблица с триггером ссылается каскадным внешним ключем, то в after statement часть коллекция придет ПУСТОЙ, хотя и будет заполняться в each row частях.
Данный баг присутствует в 11.2.0.4, почитать про него и способы его обхода можно здесь:
community.oracle.com/thread/3823651?start=0&tstart=0
asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523633800346769343
support.oracle.com/rs?type=doc&id=1638849.1
vovik0134
Способ с составным триггером действительно интересный и похож на кунг-фу. Но для решения повседневных задач, где нужно избежать мутирующих таблиц, лучше использовать хранимые процедуры (если есть такая возможность). Иначе будет не так просто понять почему художники стали ниньзя.
Alhymik
Вы имеете в виду хранимки вообще вместо триггеров? Возможно да, так будет наглядней. Но на скольо я помню, процедуру, например, для апдэйта можно вызывать при одиночном апдэйте, если же это массовый апдэйт большого количества строк, то вызов процедуры одиночного апдейта в курсорном цикле будет работать медленнее, чем один массовый апдэйт с триггером.
Alhymik
И второй момент — получается надо выбрать что-то одно, или использование хранимок для всех DML с таблицей или использование триггеров. На большом проекте не видел, чтоб выбор останавливали на первом. Возможно из-за аргумента приведенного в пред. посте (сегодня таблица маленькая, завтра разрослась и апдэйты стали массовыми), возможно из-за того, что он менее интуитивно понятен для вновь подключившихся к проекту, если надо сделать UPD таблицы чел может сразу не допереть, что все, оказывается, идет через хранимки (а использовать, повторюсь, надо что-то одно — хранимка или триггер, иначе могут быть траблы). Верно?
GlukKazan
Исходя из моего опыта, неверно. Никто не мешает использовать и то и другое. Обычно (речь идёт про Oracle и, опять же, из моего опыта), стараются отказаться от триггеров и весь DML выполнять через хранимки (естественно, оформленные пакетами). Также, встречается подход, когда хранимками оборачиваются и все select-ы, но это, на мой взгляд, уже перебор. Триггеры остаются на случаи какой-то уж совсем невменяемой интеграции, когда хранимками обойтись не удаётся. Главные аргументы такого подхода — производительность и мутации (именно в таком порядке). Разумеется, это надо мерять, но я не думаю, что DML со строчными триггерами будет сильно быстрее аналогичных действий, оформленных через API хранимок. Конечно массовые операции, через такое API будут работать медленно (не медленнее чем триггеры), но для массовых операций, обычно, делается другое API.