Home » SQL & PL/SQL » SQL & PL/SQL » Oracle trim function not working in trigger (merged) (Oracle9i)
Oracle trim function not working in trigger (merged) [message #428885] Fri, 30 October 2009 09:12 Go to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Hello,

I have two tables.

1. TEST
2. PIE

Both the tables are having field called sold_to. I am trying to copy
data from PIE table to TEST table. PIE table field has some space
and i wanted to trim the space and copy the table. I wanted to
use the trigger to trim the space on TEST table. Please remember,
i do not want to use TRIM function in the INSERT statement. But
some how, TRIM function is not working in trigger.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table test(sold_to varchar2(10));

Table created.

SQL> create or replace trigger trg before insert on test
  2  for each row
  3  begin
  4  :NEW.SOLD_TO  := trim(:NEW.SOLD_TO);                                       
  5  END;
  6  /

Trigger created.

SQL> desc pie
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOLD_TO                                            VARCHAR2(12)

SQL> insert into test select * from pie;
insert into test select * from pie
                        *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> insert into test select trim(sold_to) from pie;

105107 rows created.

SQL> 


Any help is highly appreciated!!!

[Updated on: Fri, 30 October 2009 09:34] by Moderator

Report message to a moderator

Re: Oracle trim function not working in trigger [message #428887 is a reply to message #428885] Fri, 30 October 2009 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Values are checked against column defintion BEFORE triggers are triggered.
No workaround.

Regards
Michel
Re: Oracle trim function not working in trigger [message #428908 is a reply to message #428887] Fri, 30 October 2009 12:15 Go to previous message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Ohh.. Thank you!!!. I did not know this. It is interesting!!!
Previous Topic: Rounding off
Next Topic: list of max open connections
Goto Forum:
  


Current Time: Mon Sep 26 17:54:32 CDT 2016

Total time taken to generate the page: 0.13333 seconds