ORA-04091: table SCMA.XX is mutating, trigger/function may not see it

Question

I have two tables- XX and YY with their triggers calling each other in case of an update.

The trigger on XX goes like this:

CREATE OR REPLACE TRIGGER SCMA.XX_RBIU
 BEFORE INSERT OR UPDATE
 ON SCMA.XX  FOR EACH ROW

-- PL/SQL BLOCK
BEGIN
    IF UPDATING THEN
            -- Only update the YY row if the branch id has 
            -- been modified on the XX row
            IF :NEW.BRANCH_ID <> :OLD.BRANCH_ID THEN 
                UPDATE YY TP
                SET TP.BRANCH_ID = :NEW.BRANCH_ID
                WHERE TP.XX_ID = :NEW.XX_ID;
            END IF;
    END IF;

    ...
    ... -- Other PL/SQL statements that do some necessary
    ... -- computation and do not use any SQL.
    ...
END;
/

And the trigger on YY goes like this:

CREATE OR REPLACE TRIGGER SCMA.YY_RBIU
BEFORE INSERT OR UPDATE
ON SCMA.YY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

v_xx_type     xx.xx_type_code%TYPE;

BEGIN

 select x.xx_type_code
    into v_xx_type
    from XX x
    where x.xx_id = :new.xx_id;

    ...
    ... -- Other PL/SQL statements that do some necessary
    ... -- computation and do not use any SQL.
    ...
END;
/

I know that the SELECT statement in the trigger YY_RBIU is giving this error. How can I code my triggers in order to avoid it?

I tried to wrap the SELECT statement in YY_RBIU inside an IF INSERTING THEN block but this does not run for any updates. How can I skip this SELECT statement if update is being called from trigger XX_RBIU?

I also tried putting PRAGMA AUTONOMOUS_TRANSACTION in XX_RBIU but it is resulting into a deadlock.

I also tried referring to this, this, this and this but couldn't get a solution.

Any help is much appreciated.

Why do you have a seemingly denormalized data model where both `xx` and `yy` have `xx_id` and `branch_id` columns? Is there any chance that you could fix the data model so that you're not storing this mapping in two different places? If not, why is it necessary to have this sort of data model? — Justin Cave, Nov 12 '13 at 03:49
@JustinCave- This is a business requirement. There are cases when `yy.branch_id` may not be the same as `xx.branch_id`, hence we have to maintain these in two different tables. Yet, the business also requires that if `xx.branch_id` is updated then the same should be reflected in `yy.branch_id`. The `yy.branch_id` may be changed later. — Rachcha, Nov 12 '13 at 03:55
This is too complex a situation for triggers, in my opinion. This is business logic that should be part of application code. — David Aldridge, Nov 17 '13 at 12:27

score 1 · Answer 1 · answered Nov 12 '13 at 04:01

One of a few reasons I avoid triggers. Basically you need to come up with a neater solution which does not have the circular trigger issues. One could be also adding the:

IF :NEW.BRANCH_ID <> :OLD.BRANCH_ID

To the YY trigger as well. But that may mean your trigger misses some genuine updates.

A hacky solution which will work is to have a new YY_flag table:

YY_FLAG
xx_id    (Primary Key)

Then in your XX trigger:

INSERT INTO yy_flag VALUES( :new.xx_id );
UPDATE YY ...
DELETE FROM yy_flag WHERE xx_id = :new.xx_id;

And in your YY trigger:

BEGIN
    SELECT count(1) INTO is_trigger FROM yy_flag WHERE  xx_id = :new.xx_id;
    IF is_trigger = 0 THEN
         SELECT FROM XX
         ...

So basically the yy_flag table will only contain a record for a given xx_id is executing a trigger. And the object is to never commit a row to the yy_flag table and oracle's normal locking should take care of all concurrency stuff.

As I said, this is very hacky, but should work if you cannot redesign your solution for whatever reason.

Hi! I appreciate the effort you have put in providing this solution. +1 from me. I have found my version of this solution and posted it up here. — Rachcha, Nov 12 '13 at 06:27

Rachcha · Accepted Answer · 2013-11-17T12:02:43.417

For me, a compound trigger worked. In this compound trigger, I removed the UPDATE statement from BEFORE EACH ROW segment and put it into AFTER STATEMENT segment and it worked. I used a collection to hold the necessary values.

Here is the trigger:

CREATE OR REPLACE TRIGGER SCMA.TRANSACTION_COMPOUND
  FOR INSERT OR UPDATE ON SCMA.XX
    COMPOUND TRIGGER

    -- DECLARE GLOBAL VARIABLES THAT WOULD BE USED ACROSS
    -- THE DIFFERENT EVENTS.
    TYPE rec_chg_br IS RECORD (
        xx_id    XX.xx_id%TYPE,
        branch_id        jbs_branch.branch_id%TYPE
    );
    TYPE t_chg_br IS TABLE OF rec_chg_br
        INDEX BY PLS_INTEGER;
    chg_branch t_chg_br;

  BEFORE EACH ROW IS
  BEGIN
    IF UPDATING THEN
            -- Only update the YY row if the branch id has 
            -- been modified on the XX row
            IF :NEW.BRANCH_ID <> :OLD.BRANCH_ID THEN
                chg_branch(:new.xx_id).xx_id := :new.xx_id;
                chg_branch(:new.xx_id).currency_id := :new.currency_id;
                chg_branch(:new.xx_id).branch_id := :new.branch_id;
            END IF;
    END IF;
    ...
    ... -- Other PL/SQL statements that do some necessary
    ... -- computation and do not use any SQL.
    ...
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- NULL; -- Do something here.
    FORALL i IN chg_branch.FIRST..chg_branch.LAST
        UPDATE YY
        SET BRANCH_ID = chg_branch(i).branch_id,
               CURRENCY_ID = chg_branch(i).currency_id
        WHERE xx_id = chg_branch(i).xx_id;
  END AFTER STATEMENT;

END TRANSACTION_COMPOUND;
/

No change was necessary in trigger YY_RBIU.

score 0 · Answer 3 · answered Nov 12 '13 at 07:56

the message because when you insert or update table xx in the trigger SCMA.XX_RBIU you also update table yy so the SCMA.YY_RBIU trigger activated and do select from xx table

in pl_sql it forbidden to update/insert and select in same time(you do it in xx table). i have this problem many time and this is soluation. good day.

ORA-04091: table SCMA.XX is mutating, trigger/function may not see it

3 Answers3