Help Please. Trying to add 1 Year onto a Date.

se7enhu · #1 (**permalink**) 06-11-09, 20:53

Hey guys, I got a table called members and Im trying to set up a trigger to add 1 YEAR onto a DATE when someone adds a new record. The SQL Command I've been trying is:

Quote:

CREATE TRIGGER setResigned
BEFORE INSERT ON members
FOR EACH ROW
BEGIN
SET NEW:dateResigned = DATE_ADD(NEW:dateJoined,INTERVAL 1 YEAR);
END;

I've been messing with this for two days, read the MySql documentation, search all over the net, no avail.

Please help. Thanks.

mike_bike_kite · #2 (**permalink**) 06-12-09, 04:52

Quote:

Originally Posted by se7enhu

Hey guys, I got a table called members and Im trying to set up a trigger to add 1 YEAR onto a DATE when someone adds a new record. The SQL Command I've been trying is:

I've been messing with this for two days, read the MySql documentation, search all over the net, no avail.

Please help. Thanks.

Why do it in a trigger? they just make your application more complicated and hide functionality. Why not just do it in the original insert ie :
Code:
insert members ( ... , dateResigned ) 
values ( ..., date_add( now(), interval 1 year ) );
The other question is do people always resign after 1 year? If so then why store dateResigned, why not just calculate dateResigned from the date joined.

Mike

Internet Services

Web Development

Digital Marketing

Consumer Tech