Oracle Triggers Update based on orderlines fulfilled how to avoid mutation error

Question

for a typical products & shipping Database I am exploring the best way to run a trigger that:

1. When an order line is set to 'Complete', a trigger is ran that:
2. Looks for any other order lines for that order.
3. If all other order lines for that order are also 'Complete'
4. Update the order header table to complete.

For clatiry: The order header table would store the overall oder total, and the orderLines table stores each product of the order.

SO far, the trigger is written as such:

CREATE OR REPLACE TRIGGER orderComplete
after update ON orderline
for each row
WHEN (new.orderline_fulfilled = 'Y')
DECLARE count NUMBER := 5;
ordersNotDone NUMBER;
BEGIN

SELECT COUNT(Orderline_fulfilled) INTO ordersNotDone
FROM orderHeader
JOIN orderline ON
orderHeader.Order_id = orderLine.Orderline_order
WHERE Order_id = :old.orderline_order
AND orderline_fulfilled = 'Y';

IF ordersNotDone = 0
THEN
UPDATE orderHeader
SET completed = SYSDATE
    WHERE orderId = :old.orderline_order;
ENDIF;

END;

This above causes the mutation error, when updating the orderline row.

Answer 1

Enforcing integrity with a trigger is inherently problematic because the RDBMS read consistency mode allows multiple changes simultaneously that cannot see each others' result.

A better solution might be to avoid denormalising the data, and rely on detecting the presence of an incomplete order line to identify incomplete orders. As this would be the minority of cases it can be optimised with a function-based index along the lines of:

create index my_index on orderline(case orderline_complete when 'NO' then orderid else null end)

This will index only the values of orderline where orderline_complete is 'NO', so if there are only 100 such rows in the table then the index will only contain 100 entries.

Identifying incomplete orders is then a matter only of a full or fast full index scan of a very compact index with a query:

select distinct
  case orderline_complete when 'NO' then orderid else null end orderid
from
  orderline
where
  case orderline_complete when 'NO' then orderid else null end is not null;

Answer 2

The simplest answer is to use a slightly different type of trigger which is triggered not after a row is updated but after the table is updated. This will not suffer from this problem.

So do something like:

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT ON orderline --note no for each row
BEGIN

  --loop over all orders which contain no unfulfilled orders
  FOR lrec IN (SELECT order_id FROM orderline group by order_id where not exists (select 1 from orderline where orderline_fulfilled = 'Y'))
  LOOP
    -- do stuff to order id because this are complete
  END LOOP;
END;

So here we may have completed multiple orders on the insert so the trigger needs to cope with this. Sorry I do not have an oracle instance to play with at home. Hope this helps

Answer 3

If you are using 11g then check about compound triggers, example: http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TRGC1/Default.aspx