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