create or replace trigger quantity_ck
    before insert
    on orders
    for each row
declare
    amt_ordered integer;
    item_ordered integer;
    amt_in_stock integer;

begin
    
    /*=====
        :new.colName lets you grab that colName from
        the insert that triggered this
    =====*/

    amt_ordered := :new.order_quantity;

    item_ordered := :new.item_num;

    select item_quantity
    INTO   amt_in_stock
    from   inventory
    where  item_num = item_ordered;    

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

    end if;

    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 IT WORKED!!');
    
    else
        
        raise_application_error ( -20601, 
            'Order number ' || :new.order_num || ' cannot be placed, '
            || 'because order quantity: ' || amt_ordered 
            || ' is more than the stock on hand of ' || amt_in_stock);

    end if;
end;
/

-- show any syntax errors

show errors