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" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
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/[EMAIL PROTECTED]
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
