[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle Trigger to Force Lowercase

Posted on 2004-10-29
9
Medium Priority
?
932 Views
Last Modified: 2012-08-13
Hello,

I am trying to find out how to write a trigger asap that can force all of the data going into one of my tables to be entered as lowercase, along with the unique constraint, to make sure that all data is, in fact unique. Can anyone help?
0
Comment
Question by:liltyga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 3

Accepted Solution

by:
ToddBarry earned 1200 total points
ID: 12447063
create or replace trigger t_lowercase
before insert or update on t
for each row
begin
  :new.col1 := lower(:new.col1);
  :new.col2 := lower(:new.col2);
end;

You could add conditional checks if you want:

if :new.col1 is not null and :new.col1 <> lower(:new.col1) then
  :new.col1 := lower(:new.col1);
end if;
0
 
LVL 3

Assisted Solution

by:oratim
oratim earned 800 total points
ID: 12447090

create or replace trigger <trigger_name>
Before Insert or Update on <Table_Name>
for each row
begin
 :new.<Col_Name> = lower(:new.<col_name>);

  etc... for each column on the table

end;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12447425
You have two suggestions already on converting values to lower case.  Don't use a trigger though to enforce uniqueness - that is not the best way to do that.  Use a primary or unique constraint on the column(s) instead.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:liltyga
ID: 12447483
thanks everyone!  for markgeer - i do have a unique constraint and primary key in place, but the values are still case insensitive, so I wanted this to ensure all possibilities of dupe data are avoided.
0
 
LVL 3

Expert Comment

by:ToddBarry
ID: 12447569
I guess you didn't like my answer?
0
 

Author Comment

by:liltyga
ID: 12448024
I'm sorry ToddBarry! I didnt' see the top answer - I can create a separate ticket and give you points, if that wil be helpfule
0
 
LVL 3

Expert Comment

by:ToddBarry
ID: 12448082
No big deal, but it's unfortunate when someone takes the time to reply and the answer isn't even seen.
0
 

Author Comment

by:liltyga
ID: 12453597
nothing personal - I was in a time crunch and was rushing to get the job done- I put in a request with Support to split the existing points or allocate new points. we'll see what transpires.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question