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

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