-- CS 328 - Week 1 Lab 2
-- last modified: 2016-01-22
-- your name

-- this trigger only permits orders to be inserted
--    if their quantity is reasonable,
--    and if they are, update the inventory accordingly

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
    -- set some local variable for convenience

    amt_ordered := :new.order_quantity;
    item_ordered := :new.item_num;

    select item_quantity
    into amt_in_stock
    from inventory
    where item_num = item_ordered;

    -- prevent this insert if the order is not for at least
    --    1 item

    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;

    -- allow this insertion and update inventory
    --    IF I have enough in stock for this order

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

        dbms_output.put_line('yay!!! updated inventory!');
    else
        raise_application_error( -20601, 
             'insufficient stock on stand - order rejected');
    end if;
end;
/
show errors