How to avoid ORA-04091 error when unicity check has to be done on a trigger?
Jan
17
Written by:
1/17/2013 3:02 PM
In some cases, it is not possible to add very easily unicity check using a key on an index. For example, when conditions have to be used and that the unicity concerns only some records and not all.
The solution is then to use a trigger. But if you use such a solution, some statements have to be added to avoid the Oracle mutating (ORA-04091 error) problem. This error occurs when we try to make a query on a table on which we are trying to modify some records.
For example, considering the following code:
01.CREATE OR REPLACE TRIGGER MyTriggerOnMyKey
02.BEFORE INSERT OR UPDATE ON MyTable
03.FOR EACH ROW
04.DECLARE
05. l_ident NUMBER;
06.BEGIN
07. IF LENGTH(:NEW.MYKEY)>0 THEN
08. SELECT t.IDENT
09. INTO l_ident
10. FROM MyTable t WHERE t.MYKEY=:NEW.MYKEY;
11. IF l_ident IS NOT NULL AND l_ident<>:NEW.IDENT THEN
12. RAISE_APPLICATION_ERROR(-20002, 'Error: Unicity fails on mykey=' || :NEW.MYKEY);
13. END IF;
14. END IF;
15.END;
It will generate an error each time that you will try to make an update on the table (update mytable myfield=myfield+1 where ident= for example). The code was simplified in order to show what type of queries could fail in the trigger. Of course, such a trigger could, in the above case, be replaced by a simple unicity constraint.
Anyway, in order to avoid mutating error, the statement pragma autonomous_transaction has to be added in that way:
01.CREATE OR REPLACE TRIGGER MyTriggerOnMyKey
02.BEFORE INSERT OR UPDATE ON MyTable
03.FOR EACH ROW
04.DECLARE
05. pragma autonomous_transaction;
06. l_ident NUMBER;
07.BEGIN
08. IF LENGTH(:NEW.MYKEY)>0 THEN
09. SELECT t.IDENT
10. INTO l_ident
11. FROM MyTable t WHERE t.MYKEY=:NEW.MYKEY;
12. IF l_ident IS NOT NULL AND l_ident<>:NEW.IDENT THEN
13. RAISE_APPLICATION_ERROR(-20002, 'Error: Unicity fails on mykey=' || :NEW.MYKEY);
14. END IF;
15. END IF;
16.END;