/*=====
    seeking to test/demp trigger quantity_ck
=====*/

spool trigger_test.txt

set serveroutput on

-- how does error message look if I try to insert a row
-- into inventory with the same primary key as an existing row?

prompt ========
prompt this next insert SHOULD FAIL (duplicate primary key):

insert into inventory
values
('4', 'thingamabob', '500', 5.55);

-- TEST 1 --- insert an order that SHOULD be fillable.

-- part 1 of test 1 --- what is CURRENTLY in orders, inventory?

prompt ========
prompt Test 1: Inserting a fillable order
prompt ========
prompt

prompt ========
prompt  Contents of INVENTORY before Test 1

select  *
from    inventory;

prompt ========
prompt  Contents of ORDERS before Test 1:

select  * 
from    orders;

-- part 2 of test 1 --- let customer HUGHES attempt to order 10
-- of item num 1; let's make this order number 100.

prompt ========
prompt customer HUGHES tries to order 10 of item_num 1 (order number 100)

insert into orders
values
('100', 'Hughes', '1', 10);

-- part 3 of test 1 --- are tables NOW in the desired state?
-- (is there an order 100 as entered above?)
-- (are there 10 FEWER of item 1 in the Inventory table?)

prompt ========
prompt in Orders, is there now an Order 100?

select  *
from    orders;

prompt ========
prompt in Inventory, are there now 90 of item 1?

select  *
from    inventory;

-- TEST 2 --- is the order REFUSED if you try to order 
-- too much?

prompt ========
prompt Test 2: See if an insert of a too-big order FAILS
prompt ========
prompt

-- what if Tuttle tries to order 91 of item 1?

prompt ========
prompt TEST 2 --- TRY to order 91 of item_num 1 (order number 200)

insert into orders
values
('200', 'Tuttle', '1', 91);

prompt ========
prompt there should be NO order 200 here:

select  *
from    orders;

prompt ========
prompt there should STILL be 90 of item 1:

select  *
from    inventory;

-- TEST 3: could also test that it DOES allow you to order ALL
-- remaining in inventory of an item... 

prompt ========
prompt Test 3: Will an order for ALL of an item succeed?
prompt ========
prompt 

-- what if Shmoo tries to order 200 of item 2?

prompt ========
prompt TEST 3 --- TRY to order 200 of item_num 2 (order number 300)

insert into orders
values
('300', 'Shmoo', '2', 200);

prompt ========
prompt there should be an order 300 here:

select  *
from    orders;

prompt ========
prompt there should be 0 of item 2:

select  *
from    inventory;

-- TEST 4: does it now fail if an order for a negative
-- number of items is attempted?

prompt ========
prompt Test 4: Will an order for a NEGATIVE number of items succeed?
prompt ========

-- what if Bob tries to order -23 of item 4?

prompt ========
prompt TEST 4 --- TRY to order -23 for item_num 4 (order number 400)
prompt does this fail?

insert into orders
values
('400', 'Bob', '4', -23);

prompt ========
prompt there should be NO order 400 here:

select  *
from    orders;

prompt ========
prompt there should be NO change to quantity of item 4 here:

select  *
from    inventory;

spool off   

-- end of quantity_ck-test.sql