Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_OUTPUT.PUT_LINE in Trigger
DBMS_OUTPUT.PUT_LINE in Trigger [message #308483] Mon, 24 March 2008 09:05 Go to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Hi All,
I am new to trigger and this is my first post in this forum. I have written a trigger and code is given below. When I run this trigger I am not getting any exception or error message if the ID < 100..Please help me identify the problem.

code:

create or replace
trigger "T_EMP_TRIGGER"
before insert or update on THAN_EMP
for each row
declare
my_error EXCEPTION;
begin

if :new.ID < 100 then
dbms_output.put_line('Value should be higher than 100') ;
raise my_error;
end if;
EXCEPTION
When my_error then
dbms_output.put_line('Value should be higher than 100') ;
end;
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308484 is a reply to message #308483] Mon, 24 March 2008 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Read & follow posting guidelines as stated in URL below
http://www.orafaq.com/forum/t/88153/0/
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308485 is a reply to message #308483] Mon, 24 March 2008 09:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Output from dbms_output gets sent to a buffer on the server. It is up to the client application to read that buffer for its session. If the client-application does not explicitly read that buffer (by calling dbms_output.read_lines), then
- you run the risk of overloading the buffer in older db-versions
- you won't see anything

One way or the other, your trigger will NOT fail!

Google for dbms_output for more information.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308488 is a reply to message #308485] Mon, 24 March 2008 09:31 Go to previous messageGo to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Hi ,
Thanks for the reply. Could you please let me know why the exception is not raised in this condition and that I am able to insert values <100.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308491 is a reply to message #308488] Mon, 24 March 2008 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The exceptions is raised but you trap and gobble it in your trigger, exception block.
NEVER ever use "when others then" without RAISE.

Regards
Michel
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308495 is a reply to message #308491] Mon, 24 March 2008 09:48 Go to previous messageGo to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Ok. So that means if I wouldn't have traped the exception then I would have got and error. Is my view correct.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308498 is a reply to message #308495] Mon, 24 March 2008 09:55 Go to previous messageGo to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Well I have another doubt. Should I always use RAISE_APPLICATION_ERROR in trigger or I can also use user defined exception.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308499 is a reply to message #308498] Mon, 24 March 2008 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you want.
RAISE_APPLICATION_ERROR is also a user defined exception.
Use user defined exception when you have to propagate error between procedures in the same package, use raise_application_error when you want to propagate an error outside and display it to caller. It just a hint, not an absolute rule.

Regards
Michel

[Updated on: Mon, 24 March 2008 10:01]

Report message to a moderator

Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308508 is a reply to message #308499] Mon, 24 March 2008 10:41 Go to previous messageGo to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
thanks for the reply and help me know triggers and excption better.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308509 is a reply to message #308508] Mon, 24 March 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Help you in what? Just apply his to your trigger.
Don't use dbms_output in real application, just for debugging.

Regards
Michel

[Updated on: Mon, 24 March 2008 10:47]

Report message to a moderator

Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308511 is a reply to message #308509] Mon, 24 March 2008 10:56 Go to previous messageGo to next message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Yup.I think I should display error messages from triggers with the raise_application_error.
Could you please let me know why one should not use "when others" without "raise"
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308513 is a reply to message #308511] Mon, 24 March 2008 11:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you don't know up front which error will cause the exception caught by the WHEN OTHERS, you cannot know for sure that you will want to continue, so you have to reraise.

By the way, your problem can also be handled using a check-constraint.
Re: DBMS_OUTPUT.PUT_LINE in Trigger [message #308515 is a reply to message #308513] Mon, 24 March 2008 11:12 Go to previous message
thana007
Messages: 7
Registered: March 2008
Location: India
Junior Member
Thanks Frank!! Smile
Previous Topic: complex procedure to count spaces
Next Topic: Clean up multiple sum queries
Goto Forum:
  


Current Time: Tue Dec 06 10:17:49 CST 2016

Total time taken to generate the page: 0.05360 seconds