List:General Discussion« Previous MessageNext Message »
From:Jesse Date:December 28 2005 5:56pm
Subject:Re: Need Help Writing a Trigger
View as plain text  
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
>
> 

Thread
Need Help Writing a TriggerJesse27 Dec
  • Re: Need Help Writing a TriggerJohn Meyer27 Dec
  • Re: Need Help Writing a TriggerJesse28 Dec
    • Re: Need Help Writing a TriggerPeter Brawley28 Dec
  • Re: Need Help Writing a TriggerJesse28 Dec
    • Re: Need Help Writing a TriggerPeter Brawley28 Dec
  • Re: Need Help Writing a TriggerJesse28 Dec