-- order_amount.sql

CREATE OR REPLACE TRIGGER order_amount
  AFTER INSERT OR UPDATE OF quantity, discount
  ON order_item
  FOR EACH ROW
DECLARE
  new_amount order_header.amount_due%TYPE;
  item_price product.price%TYPE;
BEGIN
  SELECT price
    INTO item_price
    FROM product
   WHERE product_id = :new.product_id;

  new_amount := :new.quantity * item_price * (1 - :new.discount);

  IF UPDATING THEN
    new_amount := new_amount - 
       (:old.quantity * item_price * (1 - :old.discount));
  END IF;

  UPDATE order_header
     SET amount_due = NVL(amount_due, 0) + new_amount
  WHERE invoice_number = :new.invoice_number;
END;
/
