Re: Need Help Writing a Trigger

[Peter Brawley]

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