Oracle PL/SQL Tutorial/Trigger/Deleting

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

   <source lang="sql">

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></source>

Is a Deleting action or an updating action

   <source lang="sql">

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.</source>