Ошибка мутирующих таблиц в триггере (ORA-04091), когда запросе участвуют таблицы Master-Detail

#sql #oracle #plsql #триггер

Помогите исправить триггер:

CREATE OR REPLACE TRIGGER OPTT.TBDR_PROTOCOL
AFTER DELETE
ON OPTT.PROTOCOL 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
   delete from RULE 
   where  RULE.OWNERENTITYGUID = 'tblJBLJOL4TFBAHLBEWZMLSHXYGMM'--this table idx
   and    RULE.OWNERPID = :old.ID
   and    RULE.CLASSGUID = 'acl4ZWIAW7W45H4HKTIPF6W6IQZTA';

   exception when NO_DATA_FOUND then
       null;    
end;
/


Где, RULE - master таблица, а PROTOCOL - любая таблица, не является для RULE detail
таблицей

При выполнении получается ошибка:


  ORA-04091: table OPTT.TESTCASESTEPRULE is mutating, trigger/function
  may not see it


Где OPTT.TESTCASESTEPRULE - является detail таблицей для RULE, в триггере таблица
PTT.TESTCASESTEPRULE не участвует в запросах

Причем delete RULE можно спокойно заменить на select* from RULE ошибка останется
такая же. 

Спасибо заранее!
    

Ответы

Ответ 1

TL;DR: Не надо использовать триггер там, где в этом нет никакой необходимости.  

Удалите триггер, перенесите логику из триггера в то место, где вызывается DML выражение,
которое приводит к срабатыванию триггера. Создайте там  процедуру или функцию, если
эта логика используется не единожды в приложении.



Перед тем как создать триггер, надо убедится в его необходимости ознакомившись с
рекомендациями по применению триггеров, например в офф. документации.

Само появление ошибки - ORA-04091: table is mutating, говорит, что допущена логическая
ошибка в дизайне кода и БД вынуждена прибегнуть к защите от возможной потери целостности
данных в мульти-пользовательской среде.       

Выше изложеное не раз обсуждалось на различных ресурсах, например, цитирую Тома Кайта
на спроси Тома:  


  My personal opinion -- when I hit a mutating table error, I've got a serious fatal
flaw in my logic.  
  
  Have you considered the multi-user implications in your logic? Two people inserting
at the same time (about the same time). What happens then??? Neither will see eachothers
work, neither will block -- both will think "ah hah, I am first"... 
  
  anyway, you can do too much work in triggers, this may well be that time -- there
is nothing wrong with doing things in a more straightforward fashion (eg: using a stored
procedure to implement your transaction) 


PS Кроме того, не возникнет ситуация как в вопросе, где с наибольшей долей вероятности,
поиск ошибки идёт не в том триггере, который эту ошибку действительно вызвал.


Ответ 2

Тут сложно ответить, не видя реальных связей между таблицами. Как вариант обхода
такой мутации - написать второй триггер типа statement. 
В первом триггере, который построчный - инициализировать пакетную переменную как
:old.ID, во втором, который statement - вызывать процедуру удаления, используя пакетную
переменную, установленную на первом шаге. 
Естественно, ограничение - удалять записи из rule можно только по одной.

PS. Exception тут лишний, как мне кажется.