BTW, is there a way to change this function so that it does away with the
"today" variable, and uses a field from a different database? For instance,
I have CampStartDate stored on the Config table. Can this Age function be
modified to get the "today" variable from that table instead?
Thanks,
Jesse
----- Original Message -----
From: "Peter Brawley" <peter.brawley@stripped>
To: "Jesse" <jlc@stripped>
Cc: "MySQL List" <mysql@stripped>
Sent: Wednesday, December 28, 2005 10:20 AM
Subject: Re: Need Help Writing a Trigger
> Jesse,
>
> >Therefore, instead of putting that long calculation in my query every
> time,
> >I'm looking for a simpler solution, a more automatic one.
>
> CREATE FUNCTION Age( dob DATE, today DATE ) RETURNS INTEGER
> DETERMINISTIC
> BEGIN
> RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0;
> END;
>
> PB
>
> -----
>
> Jesse wrote:
>
>> 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.
>>
>> Jesse
>>
>> ----- 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
>>> BEGIN
>>> // 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).
>>> END
>>>
>>> Can anyone give me a hand here?
>>>
>>> Thanks,
>>> Jesse
>>>
>>> --
>>> 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
>
>
