title: ORACLE Mutating Table error in one trigger, but not another; Why?
body: <p>Okay, I have two tables - ORDERS and ORDERLINES - which have essentially the same problem, with triggers on each to address the issue. The issue is that in addition to the PK with table-level uniqueness, on a field called RECID, there is another field, RECNO, which needs to be unique with relation to another field. </p>
  
  <p>The tables are FK related as follows: </p>
  
  <p><code>ORDERS.WAREHOUSEID &gt; WAREHOUSES.CUSTOMERID &gt; CUSTOMERS</code></p>
  
  <p>and </p>
  
  <p><code>ORDERSLINES.ORDERID &gt; ORDERS</code></p>
  
  <p>On <code>ORDERS</code> and <code>ORDERSLINES</code> I have <code>BEFORE INSERT</code> triggers to assign the realm-specific unique <strong>RECNO</strong>.<br>
  In <code>ORDERS</code>, <strong>RECNO</strong> needs to be unique within the realm of a <code>CUSTOMERS</code> record.<br>
  In <code>ORDERLINES</code>, <strong>RECNO</strong> needs to be unique within the realm of an <code>ORDERS</code> record.</p>
  
  <p>The trigger on <code>ORDERS</code> works perfectly fine. When a new order is inserted, it is assigned the next unique <strong>RECNO</strong> within the customer it belongs to.  </p>
  
  <p>The trigger on <code>ORDERLINES</code> on the other hand, which should assign the next unique <strong>RECNO</strong> within the order it belongs to, throws the dreaded <strong><em>{ORA-04091: table ORDERLINES is mutating, trigger/function may not see it}</em></strong> exception.</p>
  
  <p>Here is the trigger that works:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER ORDERS_BI 
  BEFORE INSERT ON ORDERS 
  FOR EACH ROW
  DECLARE
      CUSTID  WAREHOUSES.CUSTOMERID%TYPE;
  BEGIN
      SELECT MIN(CUSTOMERID) INTO CUSTID FROM WAREHOUSES 
          WHERE NVL(WARE_ID, '-') = NVL(:NEW.WAREHOUSEID, '-');
  
      SELECT NVL(MAX(RECNO), 0) + 1
          INTO :NEW.RECNO
          FROM deploy.ORDERS O
          LEFT JOIN deploy.WAREHOUSES W
              ON NVL(W.REC, '-') = NVL(O.WAREHOUSEID, '-')
          WHERE NVL(W.CUSTOMERID, '-') = NVL(CUSTID, '-');
  END;
  </code></pre>
  
  <p>And here is the trigger that does <strong><em>NOT</em></strong> work:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER ORDERLINES_BI 
  BEFORE INSERT ON ORDERLINES 
  FOR EACH ROW
  DECLARE
      nORDERID ORDERLINES.ORDERID%TYPE;
  BEGIN
      SELECT MIN(ORDERID) INTO nORDERID FROM REVORDERS 
          WHERE ORDERID = :NEW.ORDERID;
  
      SELECT NVL(MAX(RECNO), 0) + 1
        INTO :NEW.RECNO
        FROM deploy.ORDERLINES L
        LEFT JOIN deploy.ORDERS O
          ON O.ORDERID = L.ORDERID
        WHERE O.ORDERID = nORDERID;
  END;
  </code></pre>
  
  <p>Can SOMEONE please explain WHY the first one works, and the second one doesn't? 
  And is there some way I can re-write the second to make it work?</p>