One trigger Two Tables?

[seco]

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]

How did you create the trigger? Can you post it here?

[seco]

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]

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.