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

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