Home » SQL & PL/SQL » SQL & PL/SQL » Using WHEN OTHERS in Trigger
Using WHEN OTHERS in Trigger [message #201285] Fri, 03 November 2006 06:48 Go to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi Friends,

when i create procedure if i use 'When Others ' triggers for every Stored Procedures......

Its good or affect the performance......?


Thanx and Regards
Selva
Re: Using WHEN OTHERS in Trigger [message #201288 is a reply to message #201285] Fri, 03 November 2006 07:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It won't have any effect on performance, unless it actually gets called and you have really inefficient stuff in that section.

<Start of rant>

Personally, I hate "when others" clauses. The problem is that they suppress error messages, so you can't actually tell what has gone wrong. People write stupid things like:

exception when others then
dbms_output.put_line('An Oracle error occurred');


This is utterly useless from the point of view of finding out what went wrong and the first thing you have to do is get rid of it. Even when people capture the error message using sqlcode etc, it still isn't as good as the procedure or trigger just falling over. If it just falls over normally, it gives you useful information like the line number where it fell over. You lose all this stuff if you have an exception clause.

As to the argument that you have to have an exception clause because you don't want to see any errors, I think that is complete garbage. If you have written the trigger properly, you shouldn't get errors through it any more than through any other piece of code on the system. I've seen people put exception clauses because they are getting "mutating table" errors. If you are getting these sort of errors, it is because you have designed the trigger wrongly and suppressing it with an exception clause isn't going to help.

<End of Rant>
Re: Using WHEN OTHERS in Trigger [message #201291 is a reply to message #201288] Fri, 03 November 2006 07:08 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
what i am asking is ,

if i write code like this

when others then
dbms_output.put_line('SQLERRM'||SQLERRM);

dbms_output.put_line('SQLCODE'||SQLCODE);


It will be useful ......?

i am not talking about

' exception when others then
dbms_output.put_line('An Oracle error occurred'); '



thanx and regards
Selva

Re: Using WHEN OTHERS in Trigger [message #201293 is a reply to message #201285] Fri, 03 November 2006 07:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Read my reply again. Most of it is answered in there. For a trigger:

when others then
dbms_output.put_line('SQLERRM'||SQLERRM);

will be useless because the users probably won't see the output. If they don't see the output, you won't even know the error has occurred.

You could re-raise the error e.g.

when others then
dbms_output.put_line('SQLERRM'||SQLERRM);
raise;

But what is that going to give you that is different to the procedure just falling over normally and sending the error to the user ?

[Updated on: Fri, 03 November 2006 07:13]

Report message to a moderator

Re: Using WHEN OTHERS in Trigger [message #201296 is a reply to message #201291] Fri, 03 November 2006 07:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you missed this Line from @Cthulu's polemic

Quote:
Even when people capture the error message using sqlcode etc, it still isn't as good as the procedure or trigger just falling over. If it just falls over normally, it gives you useful information like the line number where it fell over. You lose all this stuff if you have an exception clause.



DBMS_OUTPUT is a very poor way of reporting errors - if you call the code from any front end except SQL*Plus you'll almost certainly miss it.
Use some sort of log table instead.

In general, I only use WHEN OTHER in pieces of code where it doesn't matter if there's been an error - low level functions and routines.
Anywhere else, if there's been some sort of error I didn't anticipate, it probably means something nasty's gone wrong, and we need to let the error propogate back up to the client and log it and display it there.
Re: Using WHEN OTHERS in Trigger [message #201297 is a reply to message #201293] Fri, 03 November 2006 07:17 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
ok,

If any unknown error occured, we can rollback the current transaction right.................?
For that we can go for others trigger right...

Thanx and Regards
Selva
Re: Using WHEN OTHERS in Trigger [message #201300 is a reply to message #201297] Fri, 03 November 2006 07:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But only the top level code can make the decision whether a rollback needs to be performed. If low level code performs commits or rollbacks then the user interface loses the ability to determine whether the transaction has suceeded or failed.
Re: Using WHEN OTHERS in Trigger [message #201302 is a reply to message #201300] Fri, 03 November 2006 07:30 Go to previous message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
ok,

thanks a lot

selva
Previous Topic: The "What" parameter in DBMS_JOB.SUBMIT
Next Topic: delete but still displayed in the table
Goto Forum:
  


Current Time: Fri Dec 09 01:50:28 CST 2016

Total time taken to generate the page: 0.09005 seconds