Solved

Oracle Trigger to Force Lowercase

Posted on 2004-10-29
902 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
Question by:liltyga
    8 Comments
     
    LVL 3

    Accepted Solution

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

    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 34

    Expert Comment

    by:markgeer
    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
     

    Author Comment

    by:liltyga
    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
    I guess you didn't like my answer?
    0
     

    Author Comment

    by:liltyga
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: From Zero to Hero with Nodejs & MongoDB

    Interested in Node.js, but don't know where to start or how to learn it properly? Confused about how the MEAN stack pieces of MongoDB, Expressjs, Angularjs, and Nodejs fit together? Or how it's even possible to run JavaScript outside of the browser?

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    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.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to take different types of Oracle backups using RMAN.

    845 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now