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

С уважением.
Сергей Герасимов.