ORACLE Mutating Table error in one trigger, but not another; Why?

Question

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.

The tables are FK related as follows:

ORDERS.WAREHOUSEID > WAREHOUSES.CUSTOMERID > CUSTOMERS

and

ORDERSLINES.ORDERID > ORDERS

On ORDERS and ORDERSLINES I have BEFORE INSERT triggers to assign the realm-specific unique RECNO.
In ORDERS, RECNO needs to be unique within the realm of a CUSTOMERS record.
In ORDERLINES, RECNO needs to be unique within the realm of an ORDERS record.

The trigger on ORDERS works perfectly fine. When a new order is inserted, it is assigned the next unique RECNO within the customer it belongs to.

The trigger on ORDERLINES on the other hand, which should assign the next unique RECNO within the order it belongs to, throws the dreaded {ORA-04091: table ORDERLINES is mutating, trigger/function may not see it} exception.

Here is the trigger that works:

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;

And here is the trigger that does NOT work:

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;

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?

Answer 1

I looked at your code first, rather than your explanation. My first thought was "this person is trying to fake a sequence." This obviously isn't the answer to your question but it's the reason you're getting into trouble in the first place.

The obvious solution when you're having problems faking sequences is to use a real one.

As Nicholas has already noted ORA-04091 occurs when you try to read from the table from which a trigger is fired. There are various ways to avoid this, most of which avoid trying to do something slightly funky. However, they don't influence the root cause of the error; that is you're doing something wrong. This error is normally indicative of one or both of two things:

1. You're putting far too much logic into a trigger
2. Your data-model is flawed.

The solution to the first is to move the logic to a package, which has the added benefit of removing a layer of obfuscation. The solution to the second is to normalise your database properly.

In your case, from what information you've provided, your data-model seems to be okay, though as I've said I disagree with the implementation.

This leaves you with four options to solve your problem, which I detail in order I would do them

1. Remove your triggers.
2. Replace your current logic with a sequence.
3. Remove all your trigger logic into a procedure.
4. Hack around your error.

I'm not going to discuss point 3 as you can do that yourself. Nicholas has partially covered point 4 and I'm not going to advocate something I disagree with. This leaves points 1 and 2. You say

In ORDERS, RECNO needs to be unique within the realm of a CUSTOMERS record.

This is not how you've implemented it. Your code makes RECNO consecutive within the realm of a CUSTOMERS record. The primary key of both ORDERS and ORDERLINES are by definition unique within the realm of a CUSTOMERS record.

In itself, this implies that option 1 is best for you. Remove the triggers entirely; the primary keys of the table are already doing everything you need. This also invalidates option 2; if you add a sequence then it will basically be a separate primary key.

There is no reason I can think of that you would need an order to be consecutively unique within each customer; why bother doing so?

Answer 2

You are getting that error because the second trigger is trying to read table while it is being modified. This can also happen when a trigger on a parent table causes an insert on a child table referencing a foreign key. As a quick work around create view and try to use instead of trigger. Also take a look at Tom's example of how to deal with mutating issues. Besides, if leave the second trigger as it is, any inserts into your_table select .. from table will raise mutating error. For example:

This insert will work

insert into ORDERLINES(column1, column2... columnN) 
  values(val1, val2,..., valN)

But this one wont.

insert into ORDERLINES(column1, column2... columnN) 
  select val, val..val from table