>Therefore, instead of putting that long calculation in my query every
>I'm looking for a simpler solution, a more automatic one.
CREATE FUNCTION Age( dob DATE, today DATE ) RETURNS INTEGER
RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0;
> It's not as simple as that. First, if you subtract the curdate() from
> the birthday (or vice versa), you end up with some large number that
> isn't the actual age at all. So, the calculation is a bit more
> complicated than that.
> Also, I'm not interested in their current age, but their age at the
> time of camp, which means that I need to pull a date from a config
> file. Therefore, instead of putting that long calculation in my query
> every time, I'm looking for a simpler solution, a more automatic one.
> ----- Original Message ----- From: "Jesse" <jlc@stripped>
> To: "MySQL List" <mysql@stripped>
> Sent: Tuesday, December 27, 2005 4:34 PM
> Subject: Need Help Writing a Trigger
>> I'm trying to write a trigger that will update the age of a camper
>> when ever a record is updated or inserted. I have a table named
>> Campers which contains basic information about the camper as well as
>> their birthday. I have another table named Config which holds
>> various settings, including the date at which camp begins. The Age
>> field in the Campers table needs to be set based on the
>> Config.CampStartDate. So, I have the following query that does what
>> I need:
>> SELECT c.lastname,c.firstname,c.birthday,
>> (Year(co.CampStartDate)-Year(c.Birthday)) -
>> (Right(co.CampStartDate,5)<Right(c.Birthday,5)) As Age
>> from campers c, config co
>> (there is always only one record in config).
>> There are actually a few options here:
>> 1. Create a trigger that updates this field when new records are
>> inserted or updated. This will keep the field updated.
>> 2. Everywhere in my code where I need the age, I insert the
>> calculation above to include an age column.
>> 3. I write a view that includes this calculation in it and just use
>> that everywhere. However, I don't know if there are any performance
>> issues with Views or anything.
>> First, what is the best approach here? Also, if I can get the
>> trigger to work, how do I write it? I've gotten this far with it:
>> CREATE TRIGGER insUpdAge BEFORE INSERT ON Campers
>> FOR EACH ROW
>> // somehow i need to get the CampStartDate out of the Config
>> table, and store it in a variable (I think).
>> // next, I need to do an set new.age=(calculated age).
>> Can anyone give me a hand here?
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.7/214 - Release Date: 12/23/2005