Search Results

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  RssIcon

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

Search blog