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

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, insert a row into
the table. For each bid on an item, update its bid column so that as the auction proceeds,
the ts column updates to reflect the most recent bid time. When the auction ends, the
bid value is the final price and the row can be removed from the table.
To maintain a journal that shows all changes to auctions as they progress from creation
to removal, set up another table that serves to record a history of changes to the auctions.
This 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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 permits 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 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 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