CODEWITHSUNDEEP
sqloracle-databasetriggersunique-constraint
Hooplator15
Hooplator15

Reputation: 1550

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?

Upvotes: 1

Views: 256

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 6

Related Questions