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

[BACKGROUND]

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.

------------
BEGIN

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

END
------------

[ISSUE]

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.