In a few scenarios, I prefer using the data-type TIMESTAMP of MySQL, with the flag ON UPDATE CURRENT_TIMESTAMP. This is much easier and even faster than using a trigger which sets a specified column to NOW() or CURRENT_DATE(). I love this feature, when it comes to set last modified flags of tables. Now, I encountered a problem with a tagging table by using the flag. Every tag in the table has besides the tag-name, also the aggregated number of relations and a timestamp of last update (or the insert). Everything is okay, until you want to update the aggregated column, when the numbers are wrong for any reason.
The solution must be a way of disabling the flag. One could say, we would ALTER the table and remove the ON UPDATE flag, run the UPDATE and change it back, when the update is done. There are several problems with this solution: First: The table should be very small and Second: The table should be isolated, to not get any new rows nor updates. My first attempt to solve this issue was adding a new column, which gets temporarily the timestamp, and after the update we would update the timestamp with the saved value.
Not really nice, but could be the solution:
UPDATE woot SET ts_save=ts, col=<value_to_set>;
Now, we must update again to restore the timestamp. But MySQL has a better way to solve this. This was such a moment, where I said: wohoo :-D
The solution is really intuitive. You simply have to set the value again to the same column to override the internal trigger. Sometimes trial and error pays off:
UPDATE woot SET col=<value_to_set>, ts=ts;
All problems of the ALTER TABLE approach are gone. Thanks MySQL :-)
You might also be interested in the following
- Simultaneously set and delete bit flags in a database
- MySQL Wishlist
- Fast circular buffer in MySQL
- Running Standard Deviation in MySQL
Sorry, comments are closed for this article. Contact me if you want to leave a note.