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?
 
ToddBarryConnect With a Mentor Commented:
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
 
oratimConnect With a Mentor Commented:

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.