Web Analytics Made Easy -
StatCounter One trigger Two Tables? - CodingForum

Announcement

Collapse
No announcement yet.

One trigger Two Tables?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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.
    Blue ice Studios
    Vilis Systems
    Code Spuds
    PHP Scripts

  • #2
    How did you create the trigger? Can you post it here?
    Create your own electricity using homemade wind turbine and shrink your bills!

    Comment


    • #3
      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 ;
      Blue ice Studios
      Vilis Systems
      Code Spuds
      PHP Scripts

      Comment


      • #4
        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.
        Create your own electricity using homemade wind turbine and shrink your bills!

        Comment

        Working...
        X