spool ora_ex4
set echo on
set pagesize 66
set linesize 80
/* NANNETTE THACKER   */

/* create the customer table -- nkt 4/10/97 */
create table inventory
(
        part_number     varchar2(60),
        qty_on_hand     number,
        min_qty         number,
                                -- how many of the part we need on hand
                                -- before an order should be placed
                                -- for more parts
        location        varchar2(30),
                                -- the one location per part number that
                               -- the parts are located in our inventory
       CONSTRAINT pk_part_number PRIMARY KEY(part_number)
                using index TABLESPACE users
)
        STORAGE(INITIAL 1K
                NEXT 1K
                PCTINCREASE 0)
        TABLESPACE USERS;

comment on table inventory is
'created 4/10/97 by NKT. Primary key is part_number.' ;



comment on column inventory.min_qty is
'how many of the part we need on hand before an order should be placed
for more parts' ;

comment on column inventory.location is
'the one location per part number that the parts are
located in our inventory' ;


create trigger bef_alter_inventory
        before insert or update on inventory
	  for each row 
DECLARE
        min_qty_greater EXCEPTION ;
BEGIN
        IF INSERTING and :new.min_qty >= :new.qty_on_hand THEN
                raise min_qty_greater ;
        ELSIF UPDATING and :new.min_qty >= :old.qty_on_hand THEN
                raise min_qty_greater ;
	  END IF ;
EXCEPTION
        WHEN min_qty_greater THEN
                RAISE_APPLICATION_ERROR(-20001,
                 'The minimum quantity: ' || to_char(:new.min_qty) ||
                 ' is greater than or equal to the quantity on hand: '
                 || to_char(:new.qty_on_hand) ) ;
        WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20002,
                   'You IDIOT! You did something wrong!');
END ;
/


/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/


create or replace procedure add_inventory
 (gpart_number in varchar2,
 gqty_on_hand in varchar2,
 gmin_qty in number,
 glocation in varchar2)
as
check_number inventory.part_number%type ;
ishere NUMBER ;
cursor checkpart is
    --    select qty_on_hand into check_number from inventory
        select qty_on_hand from inventory
        where part_number = gpart_number  ;
BEGIN
        OPEN checkpart ;
        fetch checkpart into check_number ;
        close checkpart ;
	  -- see if record already exists
	  select count(part_number) INTO ishere 
		from inventory where part_number = gpart_number ;
	  if ishere = 0 THEN
          INSERT INTO inventory VALUES
                (gpart_number, gqty_on_hand, gmin_qty, glocation);
        ELSE
          UPDATE inventory set qty_on_hand = (gqty_on_hand +
check_number)
                where part_number = gpart_number ;
        END IF ;
	EXCEPTION
		WHEN CURSOR_ALREADY_OPEN THEN
			raise_application_error(-20025,
                                'The cursor, checkpart, is already
opened.');
/*		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');
-- don't include WHEN OTHERS, so will use the TRIGGER ERROR!  */
END ;
/
show errors ;

execute add_inventory('A16',10,8,'Bin 41');
execute add_inventory('A17',5,3,'Bin 42');
execute add_inventory('A18',10,9,'Bin 43');
execute add_inventory('A19',15,10,'Bin 44');

select * from inventory ;

/* test for trigger, with minimum quantity greater than quantity on hand
*/
execute add_inventory('A20',16,20,'Bin 45');

select * from inventory ;

/* input same numbers, adding new values */
execute add_inventory('A16',5,8,'Bin 41');
execute add_inventory('A17',2,3,'Bin 42');
execute add_inventory('A18',3,9,'Bin 43');
execute add_inventory('A19',6,10,'Bin 44');

select * from inventory ;

/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/
/* ---------------------------------------------------------------*/

drop trigger bef_alter_inventory ;
drop table inventory cascade constraints ;
drop procedure add_inventory   ;



spool off