Oracle Trigger to Force Lowercase

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?
liltygaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ToddBarryCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oratimCommented:

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
Mark GeerlingsDatabase AdministratorCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

liltygaAuthor Commented:
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
ToddBarryCommented:
I guess you didn't like my answer?
0
liltygaAuthor Commented:
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
ToddBarryCommented:
No big deal, but it's unfortunate when someone takes the time to reply and the answer isn't even seen.
0
liltygaAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.