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

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