MySQL Cookbook, 2nd Edition

Using a Trigger 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 (or 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,
  item VARCHAR(30) NOT NULL,
  bid  DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
);

The auction table contains information about the currently active auctions (items being bid on and the current bid for each auction). When an auction begins, you enter a row into the table. Its bid column gets updated for each new bid on the item. When the auction ends, the bid value is the final price and the row is removed from the table. As the auction proceeds, the ts column is updated to reflect the time of the most recent bid.

If you also want to maintain a journal that shows all changes to auctions as they progress from creation to removal, you can modify the auction table to allow multiple records per item and add a status column to show what kind of action each row represents. Or you could leave the auction table unchanged and set up another table that serves to record a history of changes to the auctions. This second strategy can be implemented with triggers. ...