Home » SQL & PL/SQL » SQL & PL/SQL » Trigger questions (merged)
Trigger questions (merged) [message #253759] Tue, 24 July 2007 15:12 Go to next message
madhu_msinus@yahoo.co.in
Messages: 12
Registered: July 2007
Location: usa
Junior Member
can any one help me out from this...

SQL> create or replace trigger statement_level
2 before update on orders
3 declare
4 vmsg varchar2(20):='statement level trigger fired';
5 begin
6 dbms_output.put_line(vmsg);
7 end satement_level;
8 /

Trigger created.

SQL> UPDATE ORDERS SET SOMECOLUMN = 'XYZ';
UPDATE ORDERS SET SOMECOLUMN = 'XYZ'
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.STATEMENT_LEVEL", line 2
ORA-04088: error during execution of trigger 'SCOTT.STATEMENT_LEVEL'
Re: trigger error [message #253761 is a reply to message #253759] Tue, 24 July 2007 15:21 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have declared a variable as a varchar2(20), while
SQL> select length('statement level trigger fired') len from dual;

       LEN
----------
        29

SQL>
trigger fundamental. [message #253763 is a reply to message #253759] Tue, 24 July 2007 15:30 Go to previous messageGo to next message
madhu_msinus@yahoo.co.in
Messages: 12
Registered: July 2007
Location: usa
Junior Member
hi,
i am new to pl/sql...here i wrote a trigger..but i wants to display the 'vmsg'..what is the condition to display that....i updated one row but i want to display 'statement level trigger fired'..what is the condition...

SQL> create or replace trigger statement_level
2 before update on orders
3 declare
4 vmsg varchar2(30):='statement level trigger fired';
5 begin
6 dbms_output.put_line(vmsg);
7 end satement_level;
8 /

Trigger created.

SQL> update orders set somecolumn = 'xyz';

1 row updated.
Re: trigger fundamental. [message #253766 is a reply to message #253763] Tue, 24 July 2007 15:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"set serveroutput on"
This must be executed before your statement to enable the output.

Regards
Michel
Re: trigger fundamental. [message #254003 is a reply to message #253763] Wed, 25 July 2007 08:13 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
dbms_output has no purpose in a trigger as you will never see any output unless you run the update statement from YOUR sql*plus session.
Re: trigger fundamental. [message #254009 is a reply to message #254003] Wed, 25 July 2007 08:38 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or from application if it handles dbms_output buffer.

Regards
Michel
Previous Topic: logic problem
Next Topic: Get min and max dates by grouping dates with 30 day range
Goto Forum:
  


Current Time: Wed Dec 07 14:56:02 CST 2016

Total time taken to generate the page: 0.24985 seconds