Problem Description
-------------------------------
You are creating a trigger that includes an exception handling block. You wish to raise a user defined error when a certain condition is met within the trigger body using keyword RAISE.
Inside your error handling block you also include a call to RAISE_APPLICATION_ERROR.
Consider this code example --
create table tmp (col1 char(40));
create table violations (col1 varchar2(30));
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
RULE_001 EXCEPTION;
BEGIN
IF :NEW.col1 = 'mike' THEN
dbms_output.put_line(:new.col1);
INSERT INTO VIOLATIONS values ('violation logged');
-- Raise rule
RAISE RULE_001;
END IF;
EXCEPTION
WHEN RULE_001 THEN
RAISE_APPLICATION_ERROR (-20001,'Guideline Violation, Rule-001.');
END;
When this trigger is executed, you receive the ora-4088 and ora-6512 errors.
ORA-04088: error during execution of trigger 'SCOTT.DEMO_RULE_001'
Solution Description
-------------------------------
You cannot use both RAISE, within the execution block of a trigger, and RAISE_APPLICATION_ERROR, within the exception block.
Explanation
------------------------
RAISE forces execution to move into the exception block.RAISE_APPLICATION_ERROR, within the exception block, terminates the program.If the trigger body does not complete, the triggering SQL statement and any
SQL statements within the trigger body are rolled back. Thus, execution completes unsuccessfully with a runtime error and it appears as if none of the code within the trigger body gets executed.
Consider this corrected code --
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
RULE_001 EXCEPTION;
BEGIN
IF :NEW.col1 = 'mike' THEN
dbms_output.put_line(:new.col1);
INSERT INTO VIOLATIONS values ('violation logged');
-- Raise rule
RAISE RULE_001;
END IF;
EXCEPTION
WHEN RULE_001 THEN
--raise_application_error(-20001, 'Guideline Violation, Rule-001.');
dbms_output.put_line('Guideline Violation, Rule-001.');
END;
Oracle Support Doc ID 103293.1