Re: Need Help Writing a Trigger
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" <[EMAIL PROTECTED]> To: "Jesse" <[EMAIL PROTECTED]> Cc: "MySQL List" 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" <[EMAIL PROTECTED]> To: "Jesse" <[EMAIL PROTECTED]> Cc: "MySQL List" 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" <[EMAIL PROTECTED]> To: "MySQL List" 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) 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 -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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" <[EMAIL PROTECTED]> To: "Jesse" <[EMAIL PROTECTED]> Cc: "MySQL List" 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" <[EMAIL PROTECTED]> To: "MySQL List" 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) 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 -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Writing a Trigger
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" <[EMAIL PROTECTED]> To: "Jesse" <[EMAIL PROTECTED]> Cc: "MySQL List" 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" <[EMAIL PROTECTED]> To: "MySQL List" 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) 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]
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" <[EMAIL PROTECTED]> To: "MySQL List" 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) 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]
Re: Need Help Writing a Trigger
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" 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) 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Writing a Trigger
On Tuesday 27 December 2005 2:34 pm, Jesse wrote: > 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: > It seems to me that you're storing redundant data. If you know their birthday, than you know their age, just subtract the birthday from today's date and you have it. -- Dr. Joseph Dolan: Isn't there a children's book about an elephant named Babar? Fletch: I don't know. I don't have any. Dr. Joseph Dolan: No children? Fletch: No, elephant books. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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) 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]
