Click here to Skip to main content
15,908,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I would like to know mainly if there is any possibility to come out of the complete execution of pl/sql program inside a trigger. It shouldn't be like any raise_application_error() or exception. It should be like return statement in a normal pl/sql program. So please help me how could I exit in a trigger manually in between a code.

What I have tried:

I tried using raise_application_error(),but in this it terminates abnormally but what i want is an insert statement should not be executed if condition is false in a before insert trigger. I tried exception but it executes insert statement which is present in another program on which trigger must be executed.
Updated 19-Mar-17 1:59am

1 solution

Write a trigger like this:

create or replace 
trigger <triggername>
before update on <tablename>
for each row
    isexception number := 0;
        <sql statements that may cause exception>
        when others then 
            isexception := 1;
/* the exception is handled in the NESTED begin-exception-end block
and you can exit normally

An example of a procedure where execution continues after DIVISION BY ZERO since exception is handled in inner block

create or replace
procedure testexcept
i number := 0;
five constant number := 5;
	for i in 1 .. 10 
			dbms_output.put_line('5 / (' || i || ' - 5)  ' || five / (i - five));
			when others then
				dbms_output.put_line('inner exeption -  divide by zero error!!');
	end loop;
end testexcept
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900