Mega Code Archive
Use trigger to keep data consistency
<![CDATA[
SQL>
SQL> create table ord
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, '14-Feb-1999', '12 noon', 'CA',1, null, 'Gift for wife');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, '14-feb-1999', '1 pm', 'CA',7, 'Rose', 'Happy Valentines Day to Mother');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Ruby', 'Happy Valentines Day to Mother');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Coy', 'Happy Valentines Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, '21-jun-1999', '12 noon', 'VS', 2, 'Fill', 'Happy Birthday from Joe');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, '1-jan-2000', '12 noon', 'DI',3, 'Laura', 'Happy New Year''s from Lawrence');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, '2-jan-2000', '12 noon', 'CA',7, 'Sara', 'Happy Birthday from Lawrence' );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, '2:30 pm', 'VG',2, 'OK', 'Happy Valentines Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, '1:30 pm', 'VG',2, 'Hi', 'Happy Birthday Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, '3:30 pm', 'CA',2, 'Jack', 'Happy Birthday Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, '3:30 pm', 'CA',2, 'Jay', 'Thanks for giving 100%!');
1 row created.
SQL>
SQL> create table ord_item
2 (
3 order_no integer
4 ,product_id integer
5 ,quantity number(4,0)
6 ,item_price number(7,2)
7 ,total_order_item_price number(9,2)
8 ,primary key (order_no ,product_id)
9 );
Table created.
SQL>
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 1, 1, 23.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(2, 8, 1, 17.48 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(3, 8, 1, 35.99 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(4, 7, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(5, 5, 1, 10.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(6, 8, 1, 22.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 1, 6, 15.00 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(7, 8, 1, 10.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(8, 8, 1, 35.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 8, 1, 65.45 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(9, 5, 1, 10.50 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(10, 3, 1, 19.95 );
1 row created.
SQL> insert into ord_item (order_no, product_id, quantity, item_price) values(11, 8, 1, 30.00);
1 row created.
SQL>
SQL> create or replace trigger tr_total_order_price
2 after
3 update of item_price or insert or delete
4 on ord_item
5 for each row
6 declare
7 v_change_in_price number;
8 v_order_no number;
9 begin
10 if inserting then
11 v_change_in_price := :new.item_price * :new.quantity ;
12 v_order_no := :new.order_no;
13 dbms_output.put_line('inserting');
14 elsif updating then
15 v_change_in_price := (:new.item_price * :new.quantity) - (:old.item_price * :old.quantity) ;
16 v_order_no := :old.order_no;
17 dbms_output.put_line('updating');
18 else
19 v_change_in_price := (:old.item_price * :old.quantity) * -1;
20 v_order_no := :old.order_no;
21 dbms_output.put_line('deleting');
22 end if;
23
24 update ord
25 set total_order_price = total_order_price + v_change_in_price
26 where order_no = v_order_no;
27 end;
28 /
Trigger created.
SQL>
SQL> update ord_item set item_price = 25 where order_no = 7 and product_id = 5 ;
1 row updated.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL> drop table ord_item;
Table dropped.
SQL>
]]>