-- CS 328 Week 1 Lab 1
-- last modified: 2016-01-22
-- author: <your name>
--         Sharon Tuttle

-- attempt a first trigger

create or replace trigger inventory_update 
   before insert
   on orders
   for each row
declare
    amt_ordered  integer;
    item_ordered inventory.item_num%TYPE;
    amt_in_stock integer;
begin
    
    /* grab some details from this insertion */

    amt_ordered := :new.order_quantity; 
    item_ordered := :new.item_num;
    
     /*   inside PL/SQL, a SELECT statement can
          be used, but usually it must have
	     an INTO clause to indicate were its
	       projected output goes,
	         typically into a local variable
     */

     select item_quantity
     into   amt_in_stock
     from   inventory
     where  item_num = item_ordered;
  
     -- don't allow an order of a 0 or neg number
     --     of items

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

     -- only permit this if there's enough in stock

     if (amt_in_stock >= amt_ordered) then
         update inventory
         set item_quantity = item_quantity - amt_ordered
         where item_num = item_ordered;

         -- NOTE: will NOT see the following output
         -- unless you SET SERVEROUTPUT ON within
	  -- sqlplus

         dbms_output.put_line('yay! updated inventory!');
    else
         raise_application_error ( -20601,
             'insufficient quantity for this order');     
    end if;
end;
/
show errors