-- last modified: 12-1-10

-- NOTE: you will NOT see the results of the dbms_output commands unless
--     you run the command:	set serveroutput on

-- this creates the trigger inventory_update, which updates the
-- quantity in the inventory table before an order is inserted into 
-- the orders table.
--
-- it complains and does not permit the orders insert if the
-- order is for more than the current quantity available.

-- instead of dropping beforehand, let's play with 'create or replace'
-- below...
-- drop trigger inventory_update;

-- EVERY TRIGGER needs to indicate BEFORE or AFTER WHAT actions
-- on WHAT table it needs to be executed....
--
-- 'for each row' means to perform trigger actions for EACH
-- individual row affected by that trigger action
--
-- think carefully: should action be done BEFORE or AFTER triggering
-- event?
--
-- note: Oracle appears to only permit one trigger per table;
-- thus, you CAN have more than one action after the 'before' or 'after';
--
-- also note: Oracle takes some pains to avoid undesirable trigger
-- cascades; beware;

create or replace trigger inventory_update
	before insert
	on orders
	for each row
declare
	-- declare section can be omitted if you do not want
	-- to declare any variables...

	amt_ordered	integer;
	item_ordered	integer;
	amt_in_stock	integer;
begin
	-- set some variables for convenience

	-- :new.colName lets you use the value of attribute colName
	-- of the newly[to be]
	-- inserted or updated row.
	--
	-- :old.colName lets you use the value of the attribute
	-- colName of the previous[to be]deleted or updated row  

	-- set variable amt_ordered to the value of
	-- attribute order_quantity in the NEWLY to-be-inserted
	-- orders row; note the := for assignment to a variable

	amt_ordered := :new.order_quantity;
                       
	-- set variable item_ordered to be the value of
	-- attribute item_num in the NEWLY to-be-inserted
	-- orders row

	item_ordered := :new.item_num;

	-- get quantity of that item in inventory
 	-- and put it into variable amt_in_stock
	-- (note use of PL/SQL 'into' clause in this
	-- otherwise-normal select statement)

	select	item_quantity
	into 	amt_in_stock 
	from	inventory
	where 	item_num = item_ordered;

	-- triggers seem to not like a select of the
	-- table the trigger is upon; try to avoid
	-- those...

	-- is it "safe" to do this insert?

	-- don't allow an order for a NEGATIVE or 0 number of items!

	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;
	
	-- don't allow an order for more of something than we have
	-- in inventory;

	if (amt_in_stock >= amt_ordered) then

		-- it is safe --- update this item's quantity
		-- in the inventory table

		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
		-- this will PREVENT the insert into orders,
		-- and will complain with the following
		-- error message!!

		raise_application_error ( -20601, 'Order number ' ||
            		:new.order_num || ' cannot be placed, '
			|| 'because order quantity ' || amt_ordered
			|| ' of item ' || item_ordered || 
			' is more than stock on hand of ' ||
			amt_in_stock);
	end if;
end;
-- this slash below seems to be necessary to "compile" trigger!
-- DO NOT FORGET THIS!!!
/	

-- NOTE --- if you get the error message, upon creating a trigger, that:
--         Warning: Trigger created with compilation errors.
-- then the following will give you information about those errors:
-- (there aren't any right now)

show errors