Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (
Triggers [message #281947] Tue, 20 November 2007 02:24 Go to next message
Messages: 71
Registered: October 2007
Location: uk
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
Messages: 20849
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: 1473
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 Oct 21 21:03:41 CDT 2016

Total time taken to generate the page: 0.17402 seconds