A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

Question

THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line.

THe trigger thus far is:

CREATE OR REPLACE TRIGGER orderComplete
after update ON orderline
DECLARE 
orderId NUMBER;
ordersNotDone NUMBER;
BEGIN

    SELECT COUNT(Orderline_fulfilled), order_id INTO ordersNotDone, orderId
    FROM orderHeader
    JOIN orderline ON
    orderHeader.Order_id = orderLine.Orderline_order
    WHERE Order_id = orderline_order
    AND orderline_fulfilled = 'N'
    GROUP BY order_id;

    if ordersNotDone = 0
    then
        UPDATE orderHeader
        set ORDER_COMPLETED = sysdate
        WHERE order_id = orderId;
    end if;

END;

Upon an update of the order line, an 'no data found' is returned- is this sugesting the orderId variable is not returning data? The queries do work, however, when taken out of the trigger and ran independently.

Answer 1

• Your trigger checks all orderlines for completeness even when only one is updated. You should use the :NEW table prefix to filter the currently updated record.
• There is no need to include the table orderHeader while counting
• There is no need for a group-by

Try the following:

CREATE OR REPLACE TRIGGER orderComplete
   AFTER UPDATE ON orderline
 FOR EACH ROW -- Added after comment by a1ex07 
 DECLARE ordersNotDone NUMBER;
 BEGIN
    SELECT COUNT(Orderline_fulfilled)
      INTO ordersNotDone
      FROM orderline
     WHERE orderline_order = :NEW.orderline_order
       AND orderline_fulfilled = 'N';

    IF ordersNotDone = 0
    THEN
        UPDATE orderHeader
        SET order_completed = sysdate
        WHERE order_id = NEW:orderline_order;
    END IF;
END;

Answer 2

If you want to keep your trigger to be as it is (statement trigger), you can do

UPDATE (
SELECT a.ORDER_COMPLETED, a.Order_id
FROM orderHeader a
WHERE NOT EXIST (
  SELECT 1 FROM OrderLine b WHERE a.Order_Id = b.Orderline_order AND   
  b.OrderLine_Fullfilled = 'N')b
)c
SET c.ORDER_COMPLETED = 'N'

Answer 3

Jeff's advice is all good ⁺¹, but I would take it a few steps further. The UPDATE and the SELECT can be combined into one statement and that statement can be moved out of the trigger into the code block (probably should be a seperate procedure) that marks an orderline with 'Y'. Something like this:

UPDATE OrderLine SET OrderLine_Fullfilled = 'N' WHERE Order_Id = vOrderId AND ... ;

UPDATE OrderHeader SET Order_Completed = sysdate WHERE Order_Id = vOrderId
AND NOT EXISTS 
   (SELECT * FROM OrderLine WHERE Order_Id = vOrderId AND OrderLine_Fullfilled = 'N');

Assuming most of your orders are complete, if you create a function based index on DECODE(OrderLine_Fullfilled,'N',Order_Id,NULL) then you could use the following faster query:

UPDATE OrderHeader SET Order_Completed = sysdate WHERE Order_Id = vOrderId
AND NOT EXISTS 
   (SELECT * FROM OrderLine WHERE DECODE(OrderLine_Fullfilled,'N',Order_Id,NULL)=vOrderId);