-
5/1 PL/SQL: SQL Statement ignored 5/56 PL/SQL: ORA-00923: FROM keyword not found
Hello
I am trying to create a trigger which would raise an application error if the amount entered into the table exceeds the available amount but I am getting errors.
Here is the trigger:
Code:
Create Trigger Check_Availability
Before Insert
On Order_Lines
For Each Row
Declare
Quantity_On_Hand Number;
New_Order_Placed Number;
Begin
Select QTY_ON_HAND INTO QUANTITY_ON_HAND,
QTY_ON_ORDER INTO New_Order_Placed
FROM Product_Inventory
Where Product_Inventory.Product_ID=:New.Product_ID;
IF Quantity_ON_Hand '<' New_Order_Placed then
raise_Application_Error (-20001, 'Out of Stock');
End if;
End;
/
Last edited by gandolf989; 02-08-2016 at 12:21 PM.
-
First of all the board for Questions about this forum is not for asking PL/SQL questions.
You can only have on INTO in a SELECT INTO statement. Your professor might appreciate
that you added the FOR UPDATE statement to prevent two sessions from claiming the
same inventory.
Although, I don't generally use the FOR UPDATE clause, it makes sense if the transaction
is short and you really want to make sure that you don't sell more inventory that what
you have on hand. This has the result of serializing the order process so that only one
person can order a given item at any moment.
Finally, you need to use code tags, but unfortunately that means that code can be interpreted
as escape characters. Hence I put single quotes around the less than. You should take them
out when you copy the code.
Code:
CREATE TRIGGER check_availability
BEFORE INSERT ON order_lines FOR EACH ROW
DECLARE
Quantity_On_Hand NUMBER;
New_Order_Placed NUMBER;
BEGIN
SELECT qty_on_hand, qty_on_order
INTO quantity_on_hand, qty_on_order
FROM Product_Inventory
WHERE Product_Inventory.Product_ID =: NEW.Product_ID
FOR UPDATE;
IF Quantity_ON_Hand '<' New_Order_Placed
THEN
raise_Application_Error (-20001, 'Out of Stock');
END IF;
END;
/
Last edited by gandolf989; 02-08-2016 at 12:28 PM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|