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.
Posted
Updated 19-Mar-17 1:59am

1 solution

Write a trigger like this:

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


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

create or replace
procedure testexcept
as
i number := 0;
five constant number := 5;
begin
	for i in 1 .. 10 
	loop
		begin
			dbms_output.put_line('5 / (' || i || ' - 5)  ' || five / (i - five));
		exception
			when others then
				dbms_output.put_line('inner exeption -  divide by zero error!!');
		end;
	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