Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30553

Trigger Timestamp




      I am having an issue here. If anyone has any ideas how to resolve it, it would be greatly appreciated.


      I have two identical tables called test and test_history. There is a timestamp column in each entitled 'modified_at' and in the 'test' table, that column will update to current_timestamp if anything in the row was changed (ON UPDATE CURRENT_TIMESTAMP). If the row was not changed, that value remains the same. This works as designed.

      On 'test' I have an AFTER UPDATE trigger that should fire ONLY if the row has changed and insert a copy of the OLD record into the 'test_history' table.

      Here is the AFTER UPDATE trigger.


      IF (NEW.modified_at <> OLD.modified_at) THEN
      INSERT INTO test_history
      SELECT * FROM test WHERE id = OLD.id;
      END IF;



      The code appears to fine, BUT the 'NEW.modified_at' value IS NOT the value of the modified_at column after an update query on a row has run. If the update results in 0 rows changed and the 'modified_at' value remains the same, 'NEW.modified_at' does not equal that value. The 'NEW.modified_at' value actually equals the current timestamp that the trigger fired.

      I know this to be the case because I inserted both OLD.modified_at and NEW.modified_at into another table and NEW.modified_at is the time that the update was performed (trigger fired), even when nothing changes in the row.

      Any help on this is greatly appreciated. I am hoping to not cycle through all the columns looking for changes in order to fire the trigger.

      Thanks in advance.




            Unassigned Unassigned
            DavidMeissner David Meissner
            0 Vote for this issue
            2 Start watching this issue



              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.