That was perfect. I had done some research, and tried set today=(select
campstartdate from config) and various other arrangements of this, but none
of it worked. Yours worked perfectly.
Thanks for the help!
Jesse
----- Original Message -----
From: "Peter Brawley" <peter.brawley@stripped>
To: "Jesse" <jlc@stripped>
Cc: "MySQL List" <mysql@stripped>
Sent: Wednesday, December 28, 2005 1:48 PM
Subject: Re: Need Help Writing a Trigger
> Jesse,
>
> >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?
>
> For flexibility's sake, it may be preferable to pass the column value to
> the function as 'today', but if you want to tie the function to that one
> column value, just move the today declaration to inside the func and have
> the func do something like (untested)...
>
> CREATE FUNCTION Age( dob DATE ) RETURNS INTEGER
> DETERMINISTIC
> BEGIN
> DECLARE today DATE;
> SELECT CampStartDate INTO today FROM config;
> RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0;
> END;
>
> You could do the same with 'dob'.
>
> PB
>
> Jesse wrote:
>
>> 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
>>>
>>>
>>
>>
>>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date: 12/27/2005
>
