Oracle. Мутирование таблицы
У меня есть таблица:
create table NX_NUMBER_POOLS
(
nbpl_id NUMBER(10) not null,
pool_start NUMBER(12) not null,
pool_end NUMBER(12) not null,
region VARCHAR2(100) not null,
branch VARCHAR2(100) not null
)
И 2 триггера
CREATE OR REPLACE TRIGGER NX_CHECK_NUMBER_POOL_TRG
after insert or update on NX_NUMBER_POOLS
for each row
begin
if (not nx_warehouse_pg.latch) then
nx_warehouse_pg.update_pool;
end if;
end;
CREATE OR REPLACE TRIGGER save_values
before insert or update on NX_NUMBER_POOLS
for each row
begin
if (not nx_warehouse_pg.latch) then
nx_warehouse_pg.pool_start := :new.pool_start;
nx_warehouse_pg.pool_end := :new.pool_end;
end if;
end;
И пакет nx_warehouse_pg
create or replace package body NX_WAREHOUSE_PG is
procedure update_pool is
begin
latch := true;
for rec in (select pool_start, pool_end, nbpl_id from NX_NUMBER_POOLS) loop
if (nx_warehouse_pg.pool_start between rec.pool_start and
rec.pool_end) then
raise_application_error(-20000,
' Начало пула не должно пересекаться с диапозоном значений другого. ' || rec.nbpl_id);
end if;
if (nx_warehouse_pg.pool_end between rec.pool_start and
rec.pool_end) then
raise_application_error(-20000,
' Конец пула не должен пересекаться с диапозоном значений другого. ' || rec.nbpl_id);
end if;
end loop;
latch := false;
exception
when others then
latch := false;
raise;
end;
end NX_WAREHOUSE_PG;
/
create or replace package NX_WAREHOUSE_PG is
----------
--Переменные для хранения изменившихся или новых значений полей pool_start и pool_end таблицы nx_number_pools
pool_start number;
pool_end number;
--Защелка для предупреждения повторного вызова триггеров.
latch boolean := false;
--Проверка начала и конца нового пула.
procedure update_pool;
end NX_WAREHOUSE_PG;
При инсерте в таблицу все ок, при апдейте ногово значения появляется ошибка мутации таблицы. В чем проблема? Насколько я понял нельзя чтобы в триггере происходила выборка из изменяемой таблицы и для этого как раз делают отдельные процедуры, которые будут этим заниматься, так я и сделал. Можете подсказать что не так и что нужно сделать? Спасибо.
Ответы (1 шт):
Выбирать из таблицы при ее UPDATE, DELETE нельзя ни в самом триггере, ни в процедурах, которые вызываются из триггера. При DELETE ситуация еще усложняется, т.к. это правило распространяется на зависимые таблицы по ключам (foreign keys).
Для обработки такой ситуации обычно используется следующий алгоритм: сначала запоминаются в массив все идентификаторы изменяемых записей, затем в триггере на действие (без FOR EACH ROW) выполняется обработка.
В ORACLE есть специальный тип триггеров для обработки данной ситуации COMPOUND TRIGGER. Они имеют секцию для хранения переменных на всем протяжении выполнения UPDATE или DELETE.
Вот ссылка на статью и документацию.