Database Reference
In-Depth Information
INSERT
INTO
auction_log
(
action
,
id
,
ts
,
item
,
bid
)
VALUES
(
'create'
,
NEW
.
id
,
NOW
(),
NEW
.
item
,
NEW
.
bid
);
CREATE
TRIGGER
au_auction
AFTER
UPDATE
ON
auction
FOR
EACH
ROW
INSERT
INTO
auction_log
(
action
,
id
,
ts
,
item
,
bid
)
VALUES
(
'update'
,
NEW
.
id
,
NOW
(),
NEW
.
item
,
NEW
.
bid
);
CREATE
TRIGGER
ad_auction
AFTER
DELETE
ON
auction
FOR
EACH
ROW
INSERT
INTO
auction_log
(
action
,
id
,
ts
,
item
,
bid
)
VALUES
(
'delete'
,
OLD
.
id
,
OLD
.
ts
,
OLD
.
item
,
OLD
.
bid
);
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 the
auc
tion_log
table contains a complete history of what occurred:
mysql>
SELECT * FROM auction_log WHERE id = 792 ORDER BY ts;
+--------+-----+---------------------+----------------+-------+
| action | id | ts | item | bid |
+--------+-----+---------------------+----------------+-------+
| create | 792 | 2014-01-09 14:57:41 | chintz pillows | 5.00 |
| update | 792 | 2014-01-09 14:57:50 | chintz pillows | 7.50 |
| update | 792 | 2014-01-09 14:57:57 | chintz pillows | 9.00 |
| update | 792 | 2014-01-09 14:58:03 | chintz pillows | 10.00 |
| delete | 792 | 2014-01-09 14:58:03 | chintz pillows | 10.00 |
+--------+-----+---------------------+----------------+-------+