Trigger to prevent an Order update if the exceeds Quantity On Hand

Question

Need to create a trigger in Oracle that will prevent an Order entry to into the ItemsOrder table if the quantity is more than what is on hand in the Items table Quantity on Hand.

This is what I have so far:

create or replace
TRIGGER check_qty
AFTER UPDATE on OrderItems
FOR EACH ROW
BEGIN
  IF(SELECT
      OrderItems.quantity, Items.quantityOnHand FROM Items
      INNER JOIN OrderItems On Items.itemID = OrderItems.itemID
      WHERE Items.quantityOnHand < OrderItems.quantity);
      raise_application_error(-20999,'The quantity amount is greater than the unite available');
  ELSE
    dbms_output.put_line('Success');
  END IF;
END

Getting the following error:

Error(2,6): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Error(4,13): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: , ; for group having intersect minus order start union where connect

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-09-26T01:24:55

I would think something like this:

create or replace trigger check_qty before update on OrderItems
for each row as
    v_quantity number;
BEGIN
    select i.quantityOnHand into v_quantity
    from items i
    where i.itemId = :new.itemId;

    if (v_quantity < :new.quantity) then
      raise_application_error(-20999, 'The quantity amount is greater than the unite available');
    end if;
end;

Trigger to prevent an Order update if the exceeds Quantity On Hand

1 Answers