Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

  • 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;
share|improve this answer
    
There is no way he can use NEW or OLD in statement-level trigger. If you want to access NEW/OLD it must be FOR EACH ROW trigger. –  a1ex07 Apr 30 '12 at 14:37
    
Thanks, didn't realize that. So is there a reason he show not add the FOR EACH ROW? –  Jeff Apr 30 '12 at 15:55
    
Surely, your solution will work for row-level trigger. I just pointed out that OP uses statement-level trigger (no idea why, maybe he has some reasons) ; in this case you cannot refer NEW or OLD –  a1ex07 Apr 30 '12 at 16:00

Jeff's advice is all good +1, 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);
share|improve this answer

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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.