Use RAISE_APPLICATION_ERROR in a trigger : Create Trigger « Trigger « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Trigger
  3. Create Trigger

Use RAISE_APPLICATION_ERROR in a trigger

    
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 or replace trigger myTrigger
  2  after delete or update or insert on emp
  3  declare
  4  begin
  5   for irec in (select deptno, count(*) emps
  6             from emp
  7             group by deptno
  8             having count(*) < 3 or count(*) > 8)
  9   loop
 10       RAISE_APPLICATION_ERROR(-20000, 'Department '||irec.deptno || ' has '||irec.emps||' emps!');
 11   end loop;
 12  end;
 13  /
SQL>
SQL> drop table emp;








Related examples in the same category

1.create or replace trigger
2.Oracle's syntax for creating a trigger based on two tables
3.Trigger on each row
4.Use Sequence in a trigger
5.Empty trigger(before insert or update or delete)
6.Create tigger on wrapper table
7.This trigger sends messages over a pipe to record inserts into myStudent.
8.Cascade inserts into myStudent into session and lecturer.
9.Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
10.Trigger Which Modifies a Mutating Table
11.Creating a Trigger with cursor inside
12.Autonumbering Trigger
13.Show errors for a trigger
14.Submit job from a trigger
15.Use sysdate and user function in a trigger