========
this next insert SHOULD FAIL (duplicate primary key):
insert into inventory
*
ERROR at line 1:
ORA-00001: unique constraint (ST10.INVENTORY_PK) violated
========
Test 1: Inserting a fillable order
========
========
Contents of INVENTORY before Test 1
ITEM_NUM ITEM_NAME ITEM_QUANTITY ITEM_PRICE
---------- --------------- ------------- ----------
1 widget 100 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
========
Contents of ORDERS before Test 1:
no rows selected
========
customer HUGHES tries to order 10 of item_num 1 (order number 100)
YAY IT WORKED!!
1 row created.
========
in Orders, is there now an Order 100?
ORD CUST_NAME ITEM_NUM ORDER_QUANTITY
--- -------------------- ---------- --------------
100 Hughes 1 10
========
in Inventory, are there now 90 of item 1?
ITEM_NUM ITEM_NAME ITEM_QUANTITY ITEM_PRICE
---------- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
========
Test 2: See if an insert of a too-big order FAILS
========
========
TEST 2 --- TRY to order 91 of item_num 1 (order number 200)
insert into orders
*
ERROR at line 1:
ORA-20601: Order number 200 cannot be placed, because order quantity: 91 is
more than the stock on hand of 90
ORA-06512: at "ST10.QUANTITY_CK", line 40
ORA-04088: error during execution of trigger 'ST10.QUANTITY_CK'
========
there should be NO order 200 here:
ORD CUST_NAME ITEM_NUM ORDER_QUANTITY
--- -------------------- ---------- --------------
100 Hughes 1 10
========
there should STILL be 90 of item 1:
ITEM_NUM ITEM_NAME ITEM_QUANTITY ITEM_PRICE
---------- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
========
Test 3: Will an order for ALL of an item succeed?
========
========
TEST 3 --- TRY to order 200 of item_num 2 (order number 300)
YAY IT WORKED!!
1 row created.
========
there should be an order 300 here:
ORD CUST_NAME ITEM_NUM ORDER_QUANTITY
--- -------------------- ---------- --------------
100 Hughes 1 10
300 Shmoo 2 200
========
there should be 0 of item 2:
ITEM_NUM ITEM_NAME ITEM_QUANTITY ITEM_PRICE
---------- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 0 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
========
Test 4: Will an order for a NEGATIVE number of items succeed?
========
========
TEST 4 --- TRY to order -23 for item_num 4 (order number 400)
does this fail?
insert into orders
*
ERROR at line 1:
ORA-20600: Order number 400 cannot be placed, because order quantity: -23 must
be at least 1.
ORA-06512: at "ST10.QUANTITY_CK", line 23
ORA-04088: error during execution of trigger 'ST10.QUANTITY_CK'
========
there should be NO order 400 here:
ORD CUST_NAME ITEM_NUM ORDER_QUANTITY
--- -------------------- ---------- --------------
100 Hughes 1 10
300 Shmoo 2 200
========
there should be NO change to quantity of item 4 here:
ITEM_NUM ITEM_NAME ITEM_QUANTITY ITEM_PRICE
---------- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 0 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44