I have a similar desire like my first question regarding a DATETIME field. I have a DATETIME field in my db that I would like to DEFAULT to the exact value of another DATETIME field in the same table... However, if I write a trigger for this and the application or the user or someone accessing the table specifies a value for the second DATETIME field then I imagine the trigger would always overwrite it.... well I imagine if I take advantage of the mew.field_name and old.field_name values of the db, I could find away to allow someone to specify....
... But I guess again, it seems as though something like this should be able to be configured on the command line when inititally creating the talbe or the column without having to manually write a trigger for it... youshould be able to just say something like, for instance, " ALTER TABLE tablename ADD COLUMN start_date DATE, cut_off_date DATE DEFAULT start_date ".... It would be cool if you could do that. Ferindo On 5/19/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote:
Thanks Jay. Yeah, I'll just write a trigger... It sure would be cool though if you could say something like..." ADD COLUMN new_column VARCHAR(100) DEFAULT LOWER(new_column) ...." and then be done without having to explicitly define a trigger for it. This is for a userid field. Userid values at my org must be lowercase so we can ensure that the userid values are portable and can be used exactly as caputred within the db accross all our various systems... I just didn't want developers/admins to have to worry about making/forcing this integrity checks within the application and even if there's some person that accidentally INSERTs records on the command line without following this all-lowercase requirement. Ferindo On 5/19/06, Jay Pipes <[EMAIL PROTECTED]> wrote: > You could always use a trigger on BEFORE UPDATE/BEFORE INSERT which > changes NEW.column to LOWER(column). I know it's not declarative, but > it works. > > sheeri kritzer wrote: > > Not that I know of, but if you don't do binary (case-sensitive) > > searching then does it really matter?? You can retrieve with LOWER, or > > put it in your application, if your application needs to display it > > that way. > > > > -Sheeri > > > > On 5/18/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: > >> I have column and I want to make sure the db is always making sure > the > >> value > >> that gets input into this VARCHAR() column is always lowercase; > >> > >> Is there a way to set the value of a column within a table to > >> automatically > >> be lowercase. I know how to use the LOWER() function when performing > >> queries > >> but is there a way to define LOWER( ) within the definition of the > table > >> column itself without having the application specify LOWER( ) to any > >> value > >> passed to this column or if a record had to be manually input and the > > >> admin > >> forgot to make sure all the characters were lowercase. > >> > >> Ferindo > >> > >> > > > > -- > Jay Pipes > Community Relations Manager, North America, MySQL Inc. > Roaming North America, based in Columbus, Ohio > email: [EMAIL PROTECTED] mob: +1 614 406 1267 > > Are You MySQL Certified? http://www.mysql.com/certification > Got Cluster? http://www.mysql.com/cluster
-- Ferindo Middleton Technical Lead - Research and AUI Infrastructure Development Sleekcollar Software
