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
;