ORA-4088, ORA-6512 Using Triggers and RAISE_APPLICATION_ERROR

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