Database Reference
In-Depth Information
strategy discussed in
Recipe 9.4
that uses triggers to provide calculated values for ini‐
tializing (or updating) row columns.
The following example shows how to use triggers to simulate
TIMESTAMP
properties for
the
DATE
and
TIME
data types. (The same technique also serves to simulate
TIMESTAMP
properties for
DATETIME
for versions of MySQL older than 5.6.5, before
DATETIME
was
given automatic properties.) Begin by creating the following table, which has a non‐
temporal column for storing data and columns for the
DATE
and
TIME
temporal types:
CREATE
TABLE
ts_emulate
(
data
CHAR
(
10
),
d
DATE
,
t
TIME
);
The intent here is that when applications insert or update values in the
data
column,
MySQL should set the temporal columns appropriately to reflect the time at which
modifications occur. To accomplish this, set up triggers that use the current date and
time to initialize the temporal columns for new rows, and to update them when existing
rows are changed. A
BEFORE
INSERT
trigger handles row creation by invoking the
CUR
DATE()
and
CURTIME()
functions to get the current date and time and using those values
to set the temporal columns:
CREATE
TRIGGER
bi_ts_emulate
BEFORE
INSERT
ON
ts_emulate
FOR
EACH
ROW
SET
NEW
.
d
=
CURDATE
(),
NEW
.
t
=
CURTIME
();
A
BEFORE
UPDATE
trigger handles updates to the temporal columns when the
data
column changes value. An
IF
statement is required here to emulate the
TIMESTAMP
property that an update occurs only if the
data
value in the row actually changes from
its current value:
CREATE
TRIGGER
bu_ts_emulate
BEFORE
UPDATE
ON
ts_emulate
FOR
EACH
ROW
#
update
temporal
columns
only
if
nontemporal
column
changes
IF
NEW
.
data
<>
OLD
.
data
THEN
SET
NEW
.
d
=
CURDATE
(),
NEW
.
t
=
CURTIME
();
END
IF
;
To test the
INSERT
trigger, create a couple rows, but supply a value only for the
data
column. Then verify that MySQL provides the proper default values for the temporal
columns:
mysql>
INSERT INTO ts_emulate (data) VALUES('cat');
mysql>
INSERT INTO ts_emulate (data) VALUES('dog');
mysql>
SELECT * FROM ts_emulate;
+------+------------+----------+
| data | d | t |
+------+------------+----------+
| cat | 2014-04-07 | 13:53:32 |
| dog | 2014-04-07 | 13:53:37 |
+------+------------+----------+
Change the
data
value of one row to verify that the
BEFORE
UPDATE
trigger updates the
temporal columns of the changed row:
