title: ORA-04091: table SCMA.XX is mutating, trigger/function may not see it
body: <p>I have two tables- <code>XX</code> and <code>YY</code> with their triggers calling each other in case of an update.</p>
  
  <p>The trigger on XX goes like this:</p>
  
  <pre><code>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 &lt;&gt; :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;
  /
  </code></pre>
  
  <p>And the trigger on YY goes like this:</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>I know that the <code>SELECT</code> statement in the trigger <code>YY_RBIU</code> is giving this error. How can I code my triggers in order to avoid it?</p>
  
  <p>I tried to wrap the <code>SELECT</code> statement in <code>YY_RBIU</code> inside an <code>IF INSERTING THEN</code> block but this does not run for any updates. How can I skip this <code>SELECT</code> statement if update is being called from trigger <code>XX_RBIU</code>?</p>
  
  <p>I also tried putting <code>PRAGMA AUTONOMOUS_TRANSACTION</code> in <code>XX_RBIU</code> but it is resulting into a deadlock.</p>
  
  <p>I also tried referring to <a href="https://stackoverflow.com/questions/16800709/unusual-table-table1-is-mutating-trigger-function-may-not-see-it-error-in-oracl">this</a>, <a href="https://stackoverflow.com/questions/375968/ora-04091-table-blah-is-mutating-trigger-function-may-not-see-it">this</a>, <a href="https://stackoverflow.com/questions/2996748/mutating-trigger-function-may-not-see-it-error-during-execution-of-trigger">this</a> and <a href="http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936" rel="nofollow noreferrer">this</a> but couldn't get a solution.</p>
  
  <p>Any help is much appreciated.</p>