/*=====
  testing script for trigger order_maint

  by: Sharon Tuttle
  last modified: 2016-04-14
=====*/

spool order_maint_test_out.txt
set serveroutput on

prompt =================================================
prompt NOTE!! it turned out that calling pop-bks.sql
prompt HERE was a not-great idea; trigger tries to fire
prompt for each of the just-being-added order_line_item
prompt rows, and we DO NOT want that then!
prompt
prompt so (2016-04-14) REMOVING call to pop-bks.sql
prompt here, and just doing a commit here at the beginning
prompt and then a rollback at the end (to undo
prompt testing-related changes);
prompt
prompt if you need/want to rerun pop-bks.sql before
prompt your test, re-run it, THEN re-run your
prompt script creating trigger order_maint, THEN
prompt run this testing script again...
prompt =================================================

commit;

prompt
prompt ***********************
prompt testing order_maint
prompt ***********************
prompt

-- put in some "fake" old order_needed rows for '0805367829'
--     to make sure these AREN'T changed by the trigger
--     (only pending order_needed rows for a title should
--     be changed by an order, you see... 8-)

insert into order_needed
values
(1002, '0805367829', 10, '08-Jun-2011', '15-Jun-2011');

insert into order_needed
values
(1001, '0805367829', 10, '07-May-2010', '10-May-2010');

var results_code number;
exec :results_code := sell_book('0805367829', 11);

prompt ==============================================================
prompt title is not yet on order, although order is needed
prompt (and can see 2 fake "older" order_needed rows for this title)
prompt ==============================================================
prompt

select isbn, on_order
from title
where isbn = '0805367829';

select * 
from order_needed
where isbn = '0805367829';

prompt ========================================================
prompt simulate an order being placed for this title tomorrow
prompt ========================================================
prompt

insert into order_summary(order_num, pub_id, date_placed)
values
(11016, 147, sysdate+1);

insert into order_line_item
values
(11016, 1, '0805367829', 10, 0);

prompt ==============================================================
prompt after order of this title, is this title now shown as
prompt    on_order?
prompt ==============================================================
prompt

select isbn, on_order
from title
where isbn = '0805367829';

prompt ==============================================================
prompt ...and is JUST the LATEST order_needed date_placed now 
prompt    tomorrow?
prompt ==============================================================
prompt

select *
from   order_needed
where  isbn = '0805367829';

prompt undoing temporary testing changes

rollback;

spool off

-- end of order_maint_test.sql