Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (10.2.0.3)
Triggers [message #281947] Tue, 20 November 2007 02:24 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
How can i Convert all values existing for a column from lower case to upper case in a table and check validity

how do i add a trigger to force all lower case inserts/updates to the column to be be upper case.
Re: Triggers [message #281949 is a reply to message #281947] Tue, 20 November 2007 02:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UPDATE this_table SET
  some_column = UPPER(some_column);
would convert lower to upper case. How would you like to check validity? Values stored in a column are supposed to be valid - if they weren't Oracle wouldn't allow you to store them into a table.

Add a trigger using SQL*Plus (for example); create it using valid syntax. It should contain
:new.some_column := UPPER(:new.some_column);
Re: Triggers [message #282057 is a reply to message #281947] Tue, 20 November 2007 13:17 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
If you want to check validity, you could do that in your insert/update trigger by looking at another table or hard coding it in the trigger if the list is small enough.
Previous Topic: Recycle sequence
Next Topic: Adding Range Partitions to existing non-partition tables
Goto Forum:
  


Current Time: Fri Dec 09 01:38:44 CST 2016

Total time taken to generate the page: 0.10569 seconds