Recipe 16.6. 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.

To maintain a history of how each auction progresses, use an auction_log table with the following columns:

CREATE TABLE auction_log (   action ENUM('create','update','delete'),   id     INT UNSIGNED NOT NULL,   ts     TIMESTAMP,   item   VARCHAR(30) NOT NULL,   bid    DECIMAL(10,2) NOT NULL,   INDEX (id) ); 

The auction_log table differs from the auction table in two ways:

• It contains an action column to indicate for each row what kind of change was made.

• The id column has a nonunique index (rather than a primary key, which requires unique values). This allows multiple rows per id value because a given auction can generate many rows in the log table.

To ensure that changes to the auction table are logged to the auction_log table, create a set of triggers. The triggers should write information to the auction_log table as follows:

• For inserts, log a row-creation operation showing the values in the new row.

• For updates, log a row-update operation showing the new values in the updated row.

• For deletes, log a row-removal operation showing the values in the deleted row.

For this application, AFTER TRiggers are used, because they will activate only after successful changes to the auction table. (BEFORE triggers might activate even if the row-change operation fails for some reason.) The trigger definitions look like this:

CREATE TRIGGER ai_auction AFTER INSERT ON auction FOR EACH ROW BEGIN   INSERT INTO auction_log (action,id,ts,item,bid)   VALUES('create',NEW.id,NOW(),NEW.item,NEW.bid); END; CREATE TRIGGER au_auction AFTER UPDATE ON auction FOR EACH ROW BEGIN   INSERT INTO auction_log (action,id,ts,item,bid)   VALUES('update',NEW.id,NOW(),NEW.item,NEW.bid); END; CREATE TRIGGER ad_auction AFTER DELETE ON auction FOR EACH ROW BEGIN   INSERT INTO auction_log (action,id,ts,item,bid)   VALUES('delete',OLD.id,OLD.ts,OLD.item,OLD.bid); END; 

The INSERT and UPDATE triggers use NEW. col_name to access the new values being stored in rows. The DELETE TRigger uses OLD. col_name to access the existing values from the deleted row. The INSERT and UPDATE triggers use NOW⁠(⁠ ⁠ ⁠) to get the row-modification times; the ts column is initialized automatically to the current date and time, but NEW.ts will not contain that value.

Suppose that an auction is created with an initial bid of five dollars:

mysql> INSERT INTO auction (item,bid) VALUES('chintz pillows',5.00); mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ |              792 | +------------------+ 

The SELECT statement fetches the auction ID value to use for subsequent actions on the auction. Then the item receives three more bids before the auction ends and is removed:

mysql> UPDATE auction SET bid = 7.50 WHERE id = 792;                 ... time passes ...  mysql> UPDATE auction SET bid = 9.00 WHERE id = 792;                 ... time passes ...  mysql> UPDATE auction SET bid = 10.00 WHERE id = 792;                 ... time passes ...  mysql> DELETE FROM auction WHERE id = 792;              

At this point, no trace of the auction remains in the auction table, but if you query the auction_log table, you can obtain a complete history of what occurred:

mysql> SELECT * FROM auction_log WHERE id = 792 ORDER BY ts; +--------+-----+---------------------+----------------+-------+ | action | id  | ts                  | item           | bid   | +--------+-----+---------------------+----------------+-------+ | create | 792 | 2006-06-22 21:24:14 | chintz pillows |  5.00 | | update | 792 | 2006-06-22 21:27:37 | chintz pillows |  7.50 | | update | 792 | 2006-06-22 21:39:46 | chintz pillows |  9.00 | | update | 792 | 2006-06-22 21:55:11 | chintz pillows | 10.00 | | delete | 792 | 2006-06-22 22:01:54 | chintz pillows | 10.00 | +--------+-----+---------------------+----------------+-------+ 

With the strategy just outlined, the auction table remains relatively small, but we can always find information about auction histories as necessary by looking in the auction_log table.