Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder seco's Avatar
    Join Date
    Nov 2008
    Location
    Oregon
    Posts
    687
    Thanks
    6
    Thanked 79 Times in 77 Posts

    One trigger Two Tables?

    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.

  • #2
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How did you create the trigger? Can you post it here?

  • #3
    Regular Coder seco's Avatar
    Join Date
    Nov 2008
    Location
    Oregon
    Posts
    687
    Thanks
    6
    Thanked 79 Times in 77 Posts
    This is what i've accumulated so far from random tutorials, but nothing that really helps me.

    Code:
    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 ;

  • #4
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •