Using Raise_Application_Error:
As we have already looked in Exceptions, the Raise_Application_Error terminates the operation abruptly and comes to the SQL prompt. Next, let consider a condition such that, a record gets inserted and the trigger based on which causes another insert on another table.
For instance consider the subsequent trigger. This trigger checks for the job and the corresponding salary in the table. If there is any violation, it will reports to the user.
CREATE OR REPLACE TRIGGER CHKSAL_JOB AFTER INSERT ON EMP FOR EACH ROW create a AFTER
BEGIN
IF NOT(:NEW.JOB='MANAGER' AND (:NEW.SAL > 4000 AND :NEW.SAL <6000)) THEN
DBMS_OUTPUT.PUT_LINE('Limit for Manager is between 4000 and 6000');
END IF;
END;
After this code the trigger gets created. Whenever a record is inserted onto the table EMP which violates the condition the message is display as
INSERT INTO emp (empno,ename,deptno,mgr,job,sal) VALUES
(1234,'ARUN', 20, 7901,'MANAGER', 8000);
Limit for Manager is among 4000 and 6000
1 row created.
Even by the message is displayed; still this does not stop the record from being inserted. Now let us try to increase this as an exception with RAISE statement and the code is re-written as given below:
CREATE OR REPLACE TRIGGER CHKSAL_JOB AFTER INSERT ON EMP FOR EACH ROW
DECLARE
raise_ex EXCEPTION;
BEGIN
IF NOT(:NEW.JOB='MANAGER' AND (:NEW.SAL > 4000 AND :NEW.SAL <6000))
THEN
RAISE raise_ex;
END IF;
EXCEPTION
WHEN RAISE_EX THEN
DBMS_OUTPUT.PUT_LINE('Limit for Manager is between 4000 and
6000');
END;
The trigger is created.
After the trigger is created, when the insert statement is issued,
INSERT INTO emp(empno,ename,deptno,mgr,job,sal) VALUES
(1234,'ARUN', 20, 7901,'MANAGER', 8000);
Limit for Manager is among 4000 and 6000
1 row created.
Even this does not stop from inserting the record onto the table. RAISE_APPLICATION_ERROR is used to perform this operation. The subsequent example does not insert record into the table if the condition is not satisfied.
Example
CREATE OR REPLACE TRIGGER CHKSAL_JOB AFTER INSERT ON EMP FOR EACH ROW
BEGIN
IF NOT(:NEW.JOB='MANAGER' AND (:NEW.SAL > 4000 AND :NEW.SAL <6000))
THEN
RAISE_APPLICATION_ERROR(-20000,'MANAGER SALARY CANNOT EXCEED 8000 AND CANNOT BE LESS THAN 4000');
END IF;
END;
The output when record is inserted is:
INSERT INTO emp(empno,ename,deptno,mgr,job,sal) VALUES
(1234,'ARUN',20,7901,'MANAGER',8000);--values
INSERT INTO emp(empno,ename,deptno,mgr,job,sal) VALUES
(1234,'ARUN',20,7901,'MANAGER',8000) --values
* ERROR at line 1:
ORA-20000: the Salary of the Manager cannot exceed 8000 and cannot be less than 4000
ORA-06512: line 4 at "HEMA.CHKSAL_JOB",
ORA-04088: error during execution of trigger 'HEMA.CHKSAL_JOB'