/*=====
   consider: WHAT IF - you'd like the DBMS to
   PREVENT the insertion of an ORDER if you don't
   have enough in stock of the item being ordered?

   a trigger can do this;
=====*/

create or replace trigger quantity_ck
    before insert
    on orders
    for each row
declare
    -- declare section CAN be omitted (I think) if
    -- you have NO local variables

    amt_ordered  integer;
    item_ordered integer;
    amt_in_stock integer;
begin
   -- :new.colName lets you use/obtain the value of
   -- the attribute colName in the newly/to-be
   -- inserted or updated row

   -- :old.colName lets you use/obtain the value of
   -- the attribute colName in the previous/to-be
   -- deleted or updated row

   -- set some local variables for my convenience;
   -- NOTE -- you use := for ASSIGNMENT in PL/SQL

   amt_ordered := :new.order_quantity;
   item_ordered := :new.item_num;
   
   -- you can use SQL statements in PL/SQL subroutines!

   -- SOMETIMES PL/SQL adds additional features to
   -- these -- a SELECT can have an INTO clause
   -- such that what is selected is ASSIGNED to the
   -- variable(s) in that clause

   select item_quantity
   into amt_in_stock
   from inventory
   where item_num = item_ordered;

   -- make sure order is for a POSITIVE number of items!

   if (amt_ordered <= 0) then
       
       -- NOT allow the order by raising an application
       -- error

       raise_application_error (-20600, 
           'Order number ' || :new.order_num ||
           ' cannot be placed, because order quantity ' ||
           amt_ordered || ' must be at least 1.');
   end if;

   -- see if I have enough of that item
   -- in stock to permit this order to be inserted

   if (amt_in_stock >= amt_ordered) then
       -- HEY, it is safe to proceed!

       update inventory
       set item_quantity = item_quantity - amt_ordered
       where item_num = item_ordered;

       dbms_output.put_line('Successfully updated inventory'
           || ' item number: ' || item_ordered);

   else
      -- uh oh, ISN'T enough in stock for this order!

      raise_application_error( -20601, 'Order number ' ||
          :new.order_num || ' cannot be placed, because ' ||
          ' order quantity ' || amt_ordered || ' is more ' ||
          'than stock on hand of ' || amt_in_stock);
   end if;
end;
/
show errors

-- end of quantity_ck.sql