ORA-04091 table is Mutating

Question

i have the following 2 table: runs:

+--------+-------------+
| run_id | status      |
+========+=============+
| 1      | active      |
+--------+-------------+
| 2      | new         |
+--------+-------------+

and orders:

+----------+--------+--------------+
| order_id | run_id | order_status |
+==========+========+==============+
| 1        | 1      | finished     |
+----------+--------+--------------+
| 2        | 1      | finished     |
+----------+--------+--------------+
| 3        | 1      | active       |
+----------+--------+--------------+
| 4        | 2      | new          |
+----------+--------+--------------+
| 5        | 2      | active       |
+----------+--------+--------------+
| 6        | 2      | active       |
+----------+--------+--------------+

it is requested to implement the following logic: when all orders in the run have the same status the run status should be updated (to be the same as its orders).

for example when the the order_id = 3 status is set to 'finished' then the run_id=1 status should be set to 'finished' too. the same with order_id = 4 when it takes the status 'active' then run_id = 2 should set to 'active' too.

the procedure responsible of checking orders status and updating run status accordingly:

CREATE OR REPLACE PROCEDURE  check_and_update_run_status  (in_order_id     IN   orders.order_id%TYPE,
                                                           in_run_id       IN   runs.run_id%TYPE,
                                                           in_order_status IN   orders.order_status%TYPE)
AS
   v_update_run VARCHAR2(1) := 'N';
BEGIN
   /*query the table ORDERS and check if all orders in the given in_run_id having the same status as in_order_status: */
  SELECT CASE
           WHEN NOT EXISTS ( SELECT *
                                 FROM ( SELECT order_id,
                                               order_status
                                          FROM orders
                                         WHERE run_id = in_run_id )
                                WHERE order_status <> in_order_status )
             THEN 'Y'
           END
    INTO v_update_run
    FROM dual;

    IF v_update_run THEN
      UPDATE runs
         SET run_status = in_order_status
       WHERE run_id = in_run_id;
    END IF;

END check_and_update_run_status;

and I've created the trigger

CREATE OR REPLACE TRIGGER trigger1 
AFTER INSERT OR UPDATE OF order_status ON orders FOR EACH ROW 
BEGIN
  check_and_update_run_status( in_order_id  => :new.order_id,
                               in_run_id    => :new.run_id,
                               in_po_status => :new.order_status );
END;

the logic is failing because the error: ORA-04091: table ORDERS is mutating, trigger/function may not see it. the trigger is calling a procedure which querying the same table the trigger is invoked against.

what would the best way to solve such a problem?

Answer 1

There would be other ways to resolve mutating trigger but I would try to leverage the feature of compound trigger. Having said that, we should try to avoid triggers generally if possible and in your case call the procedure somewhere in other program units or in application code during the update of order status column in orders table as I see there is no dependency for each row here and we need to update against the run_id and not order_id.

Having said that I made some changes to the procedure as we don't need order_id parameter in this use case

CREATE OR REPLACE PROCEDURE check_and_update_run_status
(
   in_run_id       IN runs.run_id%TYPE
  ,in_order_status IN orders.order_status%TYPE
) AS
   v_update_run VARCHAR2(1) := 'N';
BEGIN
   /*query the table ORDERS and check if all orders in the given in_run_id having the same status as in_order_status: */
   SELECT CASE
             WHEN NOT EXISTS (SELECT *
                   FROM   (SELECT order_id
                                 ,order_status
                           FROM   orders
                           WHERE  run_id = in_run_id)
                   WHERE  order_status <> in_order_status) THEN
              'Y' ELSE 'N'
          END
   INTO   v_update_run
   FROM   dual;

   IF v_update_run = 'Y'
   THEN
      UPDATE runs 
         SET status = in_order_status 
       WHERE run_id = in_run_id;
   END IF;

END check_and_update_run_status;
/

And create the compound trigger and call the procedure as,

CREATE OR REPLACE TRIGGER trigger1
   FOR INSERT OR UPDATE OF order_status ON orders
   COMPOUND TRIGGER

   --table type to store the status index by run_id value
   TYPE table_a_row_data_t IS TABLE OF orders.order_status%TYPE INDEX BY PLS_INTEGER;

   -- global variable for the compound trigger
   g_row_level_data table_a_row_data_t;

   AFTER EACH ROW IS
   BEGIN
      IF NOT g_row_level_data.exists(:new.run_id)
      THEN
         g_row_level_data(:new.run_id) := :new.order_status;
      END IF;
   END AFTER EACH ROW;

   AFTER STATEMENT IS
   BEGIN
      --loop through all run_id and update the status by calling the procedure
      --here I used collection.first..collection.last as the index is run_id itself  
      FOR runid IN g_row_level_data.first .. g_row_level_data.last
      LOOP
         check_and_update_run_status(in_run_id    => runid
                                    ,in_order_status => g_row_level_data(runid));
      END LOOP;
   END AFTER STATEMENT;
END trigger1;
/

Please test and see if it meets your requirement.