| Using WHEN OTHERS in Trigger [message #201285] |
Fri, 03 November 2006 06:48  |
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  |
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 |
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 |
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 |
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 #201300 is a reply to message #201297] |
Fri, 03 November 2006 07:24 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
