ORA-04091 или как я боролся с мутирующими таблицами.
Наверное распростаненная ситуация, когда необходимо решить следующую задачу.
Есть таблица
create table test_table
( id number(10) constraint pk_id primary key,
id_parent number(10) constraint fk_id references test_table(id),
prop1 varchar2(10),
prop2 varchar2(10)
);
Вообщем таблица, в которой реализован стандартный подход к
посторению иерархического хранилища.
В строках таблицы хряняться сущности, имеющие свои свойства, и организованные в
иерархию.
Часть свойств у каждой сущности индивидуален, а часть - это свойство
сущности верхнего уровня.
Например.
Здание
Часть здания
Помещение
Часть помещения
У всех дочерних записей свойства одинаковое свойство родителя - адрес.
Встала задача - при изменении общего для родителя свойства - изменять его у всех
дочерних триггером.
Естественно, в прямую мои попытки это сделать потерпели полное фиаско - ORA-04091
или table name is mutating, trigger/function may not see it.
Вот мой путь решения данной проблеммы.
Суть его в использовании "универсальной" дополнительной таблицы и двух триггеров.
Один из них before update for each rows,
другой after update for statement.
Универсальная в кавычках, потому, что в данном случае это приемлимо только для таблиц
с одиночным primary key. Для составного надо накручивать логику и дополнять поля под
количество полей в primary key.
Работают они так.
При изменении триггер before для каждой измененной строки проверяет, а
изменились ли в данной записи общие свойства. Если да, то записывает в доп. таблицу
значение первичного ключа в одно поле и id транзакции в другое. Id транзакции необходимо,
чтобы разделить изменения разных пользователей при соответствующем уровне изоляции.
При необходимости можно добавить еще одно поле, куда записывать имя таблицы, в которой производятся
изменения,для того, чтобы в одной транзакции изменять несколько "ушастых таблиц"
Дальше триггер after анализирует дополнительную таблицу и производит изменения в исходной таблице,
зная, какие первичные ключи подверглись изменениям.
Данный механизм работает каскадно.
Важно оценить возможную степень вложенности и объемы изменяемой информации для того, чтобы корректно
установить значение OPEN_CURSORS в файле init.ora. По умолчанию = 50 открытых курсоров на сессию.
Примеры триггеров.
CREATE OR REPLACE TRIGGER lau_obj
BEFORE UPDATE
ON obj
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
temp_local_tran_id varchar2(100);
Begin
-- проверка изменения общего свойства
if <условие> then
temp_local_tran_id:=dbms_transaction.local_transaction_id;
-- запись первичного ключа в доп. таблицу
insert into temp_ehcap_obj values(:new.obj_id, temp_local_tran_id);
end if;
End;
/
-- разбор доп. таблицы в о втором триггере
CREATE OR REPLACE TRIGGER lsu_obj
AFTER UPDATE
ON obj
REFERENCING NEW AS NEW OLD AS OLD
declare
-- объявляем параметризованный курсор,
-- в котором выберем все строки, кот. сделаны текущей транзакцией
CURSOR my_tran (par_local_tran_id IN varchar2) IS
select obj_id, local_tran_id
from temp_ehcap_obj
where local_tran_id = par_local_tran_id ;
temp_local_tran_id varchar2(100);
temp_1 varchar2(30);
temp_2 date;
Begin
-- получим текущий id транзакции
temp_local_tran_id:= dbms_transaction.local_transaction_id;
-- цикл по курсору
FOR my_tran_rec IN my_tran(temp_local_tran_id) LOOP
-- выберем из основной таблицы те свойства, кот. хотим поменять у дочерних записей
select prop1, prop2
into temp_1, temp_2
from obj
where obj_id = my_tran_rec.obj_id;
-- ВАЖНО!!!!!!
-- так как подразумевается рекурсия, то операцию удаления необходимо делать
-- здесь, до UPDATE, который опять вызовет срабатывание триггера for each rows
-- если не удалим - окажемся в бесконечю цикле, но ORACLE нас спасет и выкинет при достижении
-- количества открытых курсоров, определенных параметров OPEN_CURSOR
-- удалим из пром. таблицы строку с текущим primary
delete from temp_ehcap_obj where local_tran_id = temp_local_tran_id and obj_id = my_tran_rec.obj_id ;
-- меняем основную таблицу, изменяем общие свойства для дочек.
-- в моем случае obj_whole_id - ссылка на родителя
update obj
set prop1 = temp_1,
prop2 = temp_2
where obj_whole_id = my_tran_rec.obj_id;
-- конец. ВСЕМ СПАСИБО.
END LOOP;
End;
/
Я надеюсь, что мое сбивчивое повествование поможет Вам в борьбе с мутантами.
Вопросы, предложения и пожелания буду рад услышать по адресу - gsa@baltros.ru
С уважением.
Сергей Герасимов.