| Trigger Execution failed [message #176685] |
Fri, 09 June 2006 03:09  |
mitra.kausik
Messages: 21
Registered: June 2006
|
Junior Member |
|
|
Hi Everybody,
This looked to me very simple but I don't know what wrong am I doing.
I need to write a trigger on a table that would restrict insert,update or delete on that table.
Here is the code I wrote
create or replace trigger trigg_inspect After insert or update or delete on inspect
for each row
begin
IF INSERTING then
raise_application_error(-20100,'Insert not allowed');
insert into check_audit values(:new.a);
End if;
IF updating THEN
raise_application_error(-20200,'Update not allowed');
End if;
IF Deleting THEN
raise_application_error(-20101,'Delete not allowed');
END IF;
END;
/
Now when I try to insert for eg
insert into inspect values('GHI');
ERROR at line 1:
ORA-20100: Insert not allowed
ORA-06512: at "KM.TRIGG_INSPECT", line 4
ORA-04088: error during execution of trigger 'KM.TRIGG_INSPECT'
Insert Not allowed is being show with correct error message.
But the rest of the errors I am not getting them. Looked into ora error messages
ORA-06512 at string line string
Cause: Backtrace message as the stack is unwound by unhandled exceptions.
Action: Fix the problem causing the exception or write an exception handler
for this condition. Or you may need to contact your application administrator
or database administrator.
So it seems to have served the purpose of restricting /deleting or updating but my concern is what should i do to prevent these errors from getting generated
ORA-06512: at "KM.TRIGG_INSPECT", line 3
ORA-04088: error during execution of trigger 'KM.TRIGG_INSPECT'
Thanks
|
|
|
|
| Re: Trigger Execution failed [message #176686 is a reply to message #176685] |
Fri, 09 June 2006 03:25  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Ummm...
how about Nothing.
Thoe other two errors are just part of the error stack that Oracle maintains to show you where the error came from.
In the code that atempts to modify this table you just need to trap and handle ORA-20100,ORA-20200 and ORA-20101, and everything will be fine.
|
|
|
|
|
|
| Re: Trigger Execution failed [message #176693 is a reply to message #176685] |
Fri, 09 June 2006 03:53 |
mitra.kausik
Messages: 21
Registered: June 2006
|
Junior Member |
|
|
Thank you to both of you for your advice I know if I handle the unhandled exception for raise_application_error the trigger becomes meaningless.
But is there any way so that I can hide those two errors
ORA-06512:
ORA-04088:
Regards
Kausik
|
|
|
|
|
|
| Re: Trigger Execution failed [message #176695 is a reply to message #176693] |
Fri, 09 June 2006 03:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What are you trying to hide the error messages from, and what exactly are you trying to achieve?
If another piece of code tries to modify that table, then presumably it will have an exception block, which will then catch the ORA-20100 and deal with it accordingly.
If nothing catches the exception, then it will pass all the way up to the client, which will, presumably, have some control over how the error is displayed.
|
|
|
|
| Re: Trigger Execution failed [message #176712 is a reply to message #176695] |
Fri, 09 June 2006 04:38 |
mitra.kausik
Messages: 21
Registered: June 2006
|
Junior Member |
|
|
The purpose of it is to prevent clients from updating a table.
No other piece of code will try and modify this table.
What I intend to have is this
If client issues any insert/update /delete operations then it should display appropriate messages even with ORA error
What i want to hide is the display of the above mentioned .
Is there any way to hide them.
For himangshu's
You are trying to insert into audit table after raising application error. That will also never happen for you.
Put the insert before raising error. Better perform it through autonomous transaction.
Regards
Himanshu
I agree and will do the same.
|
|
|
|
| Re: Trigger Execution failed [message #176721 is a reply to message #176712] |
Fri, 09 June 2006 04:56 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: |
The purpose of it is to prevent clients from updating a table.
No other piece of code will try and modify this table.
What I intend to have is this
If client issues any insert/update /delete operations then it should display appropriate messages even with ORA error
What i want to hide is the display of the above mentioned .
Is there any way to hide them.
|
So, are the clients going to be writing sql statements in SQL*Plus, or is there going to be some sort of Front end software involved?
In most front ends I've seen, if you don't explicitly handle the exception that gets raised, then you tend to get a pop-up window showing the whole error stack.
If you handle the exception, then you can (presumably) create you own popup window to display whaever you like, including the error code (SQLCODE) the error message raise (SQLERRM), or anything else at all. But, if you're handling the exception, then the user won't see anything except what you explicitly show him.
So, if your question is actually 'How can I do nothing to handle the exception I've raised, and yet still change the format that it is displayed to the user in' then the answer is that you can't.
If you want to handle the exception in the code then you can use SQLERRM to extract just the 'ORA-20100: Insert not allowed'
|
|
|
|
|
|
|
|
| Re: Trigger Execution failed [message #185963 is a reply to message #176685] |
Fri, 04 August 2006 09:21 |
marcodba
Messages: 5
Registered: August 2006
|
Junior Member |
|
|
I faced the same issue and found this: as exception messages are separated by newline character and as the exception message comes at the first line, I perfomed a substring to get the first line only:
--get only first line because others come from oracle
v_text:=substr(text, 1, instr(text, chr(10), 1, 1));
This will display you message only.
Marco.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
