John August John August - 3 months ago 10
SQL Question

Oracle - Getting Mutating Table Exception in trigger?

I would like to write a trigger that checks before insert on a table to see if there are existing entries in that table with the same

fk_id
and
active flag
. For example imagine the following table:

| row_id | fk_id | active_flag |
| ------------------------------|
| 1 | 500 | 1 |
| 2 | 500 | 0 |
| 3 | 501 | 1 |


Say I want to insert a new row with
fk_id = 500
and
active_flag = 1
. I want to throw an exception on this because, based on my rules, you cannot have two rows with the same
fk_id
that are also active in this table at any given time.

I wrote a trigger to try and handle this:

CREATE OR REPLACE TRIGGER MYSCHEMA.CHECK_DUPS_BIU

BEFORE INSERT OR UPDATE ON MYSCHEMA.MYTABLE_T FOR EACH ROW
DECLARE
l_cnt NUMBER;
e_dup EXCEPTION;
BEGIN

SELECT COUNT(*)
INTO l_cnt
FROM MYSCHEMA.MYTABLE_T
WHERE fk_id = :new.fk_id
AND active_flag > 0;

IF(l_cnt > 0)
THEN
RAISE e_dup;
END IF;

EXCEPTION
WHEN e_dup THEN
raise_application_error(-20300, 'You cannot insert two active items with the same fk_id');
END CHECK_DUPS_BIU;
/


With this trigger it works completely fine on inserts but when I do an update, I am getting a Mutating table exception:

ORA-04091: table MYSCHEMA.CHECK_DUPS_BIU is mutating, trigger/function may not see it
ORA-06512: at "MYSCHEMA.CHECK_DUPS_BIU ", line 12
ORA-04088: error during execution of trigger 'MYSCHEMA.CHECK_DUPS_BIU '


I did a little research on the issue and I read that a better way to deal with a situation like this *might be to use a constraint instead but I am not sure how to code such a constraint that checks on another column (the active_flag).

How can I implement such a check?

Answer

Don't use a trigger, use a UNIQUE function-based index:

create unique index myindex on mytable (case when active_flag=1 then fk_id end);

When active_flag is not 1, no entry will be stored in the index; when active_flag is 1 an entry will be stored - but an exception will be raised if the same fk_id is already there.