...

View Full Version : One trigger Two Tables?



seco
08-25-2011, 09:47 AM
I need to update the "date_modified" field of a table when I add or edit a record from another table.

The date_modified is a mysql datetime type.

Im just not finding anything that will work searching on google or perhaps im not searching correctly? Any help would be thankful.

If you know what to do please look below..


Note gets created or edited
/////////////////////////
table: Notes
fields: opp_id, title, notes, ......
////////////////////////

date_modified field updates to the current datetime.
////////////////////////
table: Opportunities
fields: id, author_name, date_created, date_modified.....
//////////////////////////



this is my first time trying triggers so please go easy on me.

thanks.

GreenClover
08-25-2011, 10:00 AM
How did you create the trigger? Can you post it here?

seco
08-25-2011, 10:02 AM
This is what i've accumulated so far from random tutorials, but nothing that really helps me.


DELIMITER $$
CREATE TRIGGER after_note_update
AFTER UPDATE ON notes
FOR EACH ROW BEGIN
INSERT INTO opportunities
SET action = 'update',
date_modified = OLD.date_modified,
date_modified= OLD.date_modified,
date_modified = NOW(); END$$
DELIMITER ;

GreenClover
08-26-2011, 02:19 AM
Did the trigger get create but not executed or did you get error when creating the trigger? Also, why did you update date_modified 3 times? I believe the last one will do if you want to update it to the current date & time.



DELIMITER $$
CREATE TRIGGER after_note_update
AFTER UPDATE ON notes
FOR EACH ROW BEGIN
INSERT INTO opportunities
SET action = 'update',
date_modified = NOW();
END;
$$
DELIMITER ;


Also, the trigger above is only for when you update 'notes' table but will not run when you insert new row in the 'notes' table. You need to create another trigger for 'AFTER INSERT'.

You also missed a semicolon after 'END'. Not sure if that is causing the problem.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum