Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Execution failed
Trigger Execution failed [message #176685] Fri, 09 June 2006 03:09 Go to next message
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 Go to previous messageGo to next message
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 #176687 is a reply to message #176685] Fri, 09 June 2006 03:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ORA-06512: at "KM.TRIGG_INSPECT", line 4
ORA-04088: error during execution of trigger 'KM.TRIGG_INSPECT'


Becoz ..

Error raised due to raise_application_error is unhandled ...

And If you handled the exception

No error Message will be produced and transaction that fired the triggers will be allowed to take place..
[ That is the Trigrger becomes Meaningless ] Laughing Laughing Laughing

Thumbs Up
Rajuvan.
Re: Trigger Execution failed [message #176693 is a reply to message #176685] Fri, 09 June 2006 03:53 Go to previous messageGo to next message
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 #176694 is a reply to message #176685] Fri, 09 June 2006 03:54 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

IF INSERTING then
raise_application_error(-20100,'Insert not allowed');
insert into check_audit values(:new.a);
End if;


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
Re: Trigger Execution failed [message #176695 is a reply to message #176693] Fri, 09 June 2006 03:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #176732 is a reply to message #176685] Fri, 09 June 2006 06:11 Go to previous messageGo to next message
mitra.kausik
Messages: 21
Registered: June 2006
Junior Member
We do provide an interface in our application where user can enter or probably the DBA could enter slq statements of his choice.

Anyway thanks
Re: Trigger Execution failed [message #176922 is a reply to message #176732] Mon, 12 June 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you trust them enough to execute any sql-statement they want, trust them to know how to read the error stack.
Re: Trigger Execution failed [message #185963 is a reply to message #176685] Fri, 04 August 2006 09:21 Go to previous message
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.
Previous Topic: how to insert 45&56&
Next Topic: Slow Insert after Delete
Goto Forum:
  


Current Time: Sat Dec 10 20:55:10 CST 2016

Total time taken to generate the page: 0.27870 seconds