Oracle PL/SQL Tutorial/Trigger/Deleting

if ( updating or deleting ), if ( inserting or updating )

SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
SQL>
SQL>
SQL> alter table dept
  2  add emp_count number
  3  constraint must_be_between_3_8
  4  check(emp_count between 3 and 8 OR emp_count = 0)
  5  deferrable initially deferred;
SQL>
SQL> update dept set emp_count = (select count(*) from emp where emp.deptno = dept.deptno )
  2  /
SQL>
SQL> alter table dept
  2  modify emp_count NOT NULL;
SQL>
SQL> create trigger emp_dept_cnt_trigger
  2  after insert or update or delete on emp
  3  for each row
  4  begin
  5      if ( updating and :old.deptno = :new.deptno )
  6      then
  7          return;
  8      end if;
  9      if ( inserting or updating )
 10      then
 11          update dept set emp_count = emp_count+1
 12           where deptno = :new.deptno;
 13      end if;
 14      if ( updating or deleting )
 15      then
 16          update dept set emp_count = emp_count-1
 17           where deptno = :old.deptno;
 18      end if;
 19  end;
 20  /
SQL>
SQL> drop table emp;
SQL> drop table dept;
SQL>

Is a Deleting action or an updating action

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.