-- Trigger to manage inventory in a manufacturing (ERP) database 
-- application.

CREATE OR REPLACE TRIGGER  trig_inv_qoh
AFTER UPDATE OF quantity_on_hand  ON  mrp_inventory 
FOR EACH ROW
WHEN (NEW.quantity_on_hand < NEW.min_required_quantity)
DECLARE
  v_bo_qty    mrp_back_order.back_order_quantity%TYPE; 
BEGIN
 SELECT COUNT(*)   INTO  v_bo_qty
 FROM   mrp_back_order
 WHERE  part_num = :NEW.part_num;

 IF v_bo_qty = 0  THEN
    INSERT INTO  mrp_back_order(part_num, back_order_quantity)
     VALUES (:NEW.part_num,
      :NEW.quantity_on_hand + :NEW.min_required_quantity);
 END IF;

END trig_inv_qoh;
/