Why does 'on update CURRENT_TIMESTAMP' doesn't update when same data is updated?

Question

When i have a table with the following data:

StatementID(int AI) | created_by(int) | changed_when(onUpdate CURRENT_TIMESTAMP)
--------------------------------------------------------------------------------
7                   | 4               | 2013-02-26 12:05:57  
8                   | 4               | 2013-02-26 12:20:12

I have the following Query:

mysql_query('
  UPDATE table 
  SET created_by = 4 
  WHERE statementID=8');

When I edit the statement info(other tbl) and it is edited by the same user as last time, the changed_when doesn't update.

Why does the field changed_when not change when I update created_by with the same data?

Answer 1

This behaviour is by design. onUpdate CURRENT_TIMESTAMP fields update when the values of fields change, not when they stay the same.

To achieve what you seem to want, you can do

UPDATE table 
SET created_by = 4,
changed_when = null,
WHERE statementID = 8

Answer 2

If in any of the update statement if the value remains same then the timestamp value wont be updated.

From Mysql SITE

If the column is auto-updated, it is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. The column remains unchanged if all other columns are set to their current values. To prevent the column from updating when other columns change, explicitly set it to its current value.

Solution

To update the column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

Refer