How to get mysql to automatically update a column to the current timestamp with every row change operation?

Question

there are 2 columns in my mysql table: created_on and modified_on. I set the default value for the created_on column to CURRENT_TIMESTAMP. thus, with each insert operation in the table, the created_on column correctly sets the value to the current timestamp. Be that as it may, I want the column "modified_on" to update the value by the timestamp when it is updated when updating the same row. Let me know if I use any function / trigger to do this, or is there any mysql built-in function for the same

Answer 1

Try changing the table using the ON UPDATE keyword, for example:

 ALTER TABLE `tableName` CHANGE `modified_on` `modified_on` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 

Or you can add a default value when pasting as:

 ALTER TABLE `tableName` CHANGE `modified_on` `modified_on` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; 

You can also define ON UPDATE for the timestamp data type when creating the table.

Answer 2

Enter a trigger for this