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.