Is a Deleting action or an updating action : Deleting « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Deleting

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.