Using Stored Routines, Triggers, and Scheduled Events - MySQL - page 322

mysql> UPDATE ts_emulate SET data = 'axolotl' WHERE data = 'cat';
mysql> SELECT * FROM ts_emulate;
+---------+------------+----------+
| data | d | t |
+---------+------------+----------+
| axolotl | 2014-04-07 | 13:53:49 |
| dog | 2014-04-07 | 13:53:37 |
+---------+------------+----------+
Issue another UPDATE , but this time use one that does not change any data column
values. In this case, the BEFORE UPDATE trigger should notice that no value change oc‐
curred and leave the temporal columns unchanged:
mysql> UPDATE ts_emulate SET data = data;
mysql> SELECT * FROM ts_emulate;
+---------+------------+----------+
| data | d | t |
+---------+------------+----------+
| axolotl | 2014-04-07 | 13:53:49 |
| dog | 2014-04-07 | 13:53:37 |
+---------+------------+----------+
The preceding example shows how to simulate the auto-initialization and auto-update
properties offered by TIMESTAMP columns. To implement only one of those properties
and not the other, create only one trigger and omit the other.
9.7. Using Triggers to Log Changes to a Table
Problem
You have a table that maintains current values of items that you track (such as auctions
being bid on), but you'd also like to maintain a journal (history) of changes to the table.
Solution
Use triggers to “catch” table changes and write them to a separate log table.
Discussion
Suppose that you conduct online auctions, and that you maintain information about
each currently active auction in a table that looks like this:
CREATE TABLE auction
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
item VARCHAR ( 30 ) NOT NULL ,
bid DECIMAL ( 10 , 2 ) NOT NULL ,