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]
