List:General Discussion« Previous MessageNext Message »
From:Ferindo Middleton Date:May 19 2006 9:11pm
Subject:Re: how to default ... DATE column to another column date field, without using a trigger?
View as plain text  
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 <ferindo.middleton@stripped> 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 <jay@stripped> 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 <ferindo.middleton@stripped> 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: jay@stripped    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

Thread
Re: how to default ... DATE column to another column date field, without using a trigger?Ferindo Middleton19 May