|
SQL>
SQL> CREATE TABLE s_item
2 (order_id NUMBER(7),
3 item_id NUMBER(7),
4 product_id NUMBER(7),
5 price NUMBER(11, 2),
6 quantity NUMBER(9),
7 quantity_shipped NUMBER(9));
Table created.
SQL>
SQL> INSERT INTO s_item VALUES (100, 1, 10011, 135, 500, 500);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 2, 10013, 380, 400, 400);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 3, 10021, 14, 500, 500);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 5, 30326, 582, 600, 600);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 7, 41010, 8, 250, 250);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 6, 30433, 20, 450, 450);
1 row created.
SQL> INSERT INTO s_item VALUES (100, 4, 10023, 36, 400, 400);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 1, 30421, 16, 15, 15);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 3, 41010, 8, 20, 20);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 5, 50169, 4.29, 40, 40);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 6, 50417, 80, 27, 27);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 7, 50530, 45, 50, 50);
1 row created.
SQL> INSERT INTO s_item VALUES (101, 4, 41100, 45, 35, 35);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE s_item_log
2 (order_id NUMBER(7),
3 item_id NUMBER(7),
4 product_id NUMBER(7),
5 price NUMBER(11, 2),
6 quantity NUMBER(9),
7 quantity_shipped NUMBER(9),
8 log_type VARCHAR2(1),
9 log_user VARCHAR2(30),
10 log_date DATE);
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER bud_item
2 BEFORE UPDATE OR DELETE
3 ON s_item
4 REFERENCING OLD AS OLD NEW AS NEW
5 FOR EACH ROW
6 BEGIN
7 IF (UPDATING) THEN
8 INSERT INTO s_item_log (ORDER_ID, ITEM_ID, PRODUCT_ID,
9 PRICE, QUANTITY, QUANTITY_SHIPPED,
10 LOG_TYPE, LOG_USER, LOG_DATE )
11 VALUES ( :OLD.ORDER_ID, :OLD.ITEM_ID, :OLD.PRODUCT_ID,
12 :OLD.PRICE, :OLD.QUANTITY, :OLD.QUANTITY_SHIPPED,
13 'U', USER, SYSDATE );
14 ELSIF (DELETING) THEN
15 INSERT INTO s_item_log ( ORDER_ID, ITEM_ID, PRODUCT_ID,
16 PRICE, QUANTITY, QUANTITY_SHIPPED,
17 LOG_TYPE, LOG_USER, LOG_DATE )
18 VALUES ( :OLD.ORDER_ID, :OLD.ITEM_ID, :OLD.PRODUCT_ID,
19 :OLD.PRICE, :OLD.QUANTITY, :OLD.QUANTITY_SHIPPED,
20 'D', USER, SYSDATE );
21 END IF;
22 END;
23 /
Trigger created.
SQL>
SQL>
SQL> drop table s_item;
Table dropped.
SQL>
SQL> drop table s_item_log;
Table dropped.
|