The
TIMESTAMP
data type
provides auto-initialization and auto-update properties. You would
like to use these properties for other temporal data types, but the
other types allow only constant values for initialization, and they
don’t auto-update.
Use an INSERT
trigger to
provide the appropriate current date or time value at record creation
time. Use an UPDATE
trigger to
update the column to the current date or time when the row is
changed.
Using TIMESTAMP to Track Row Modification Times describes the
special initialization and update properties of the TIMESTAMP
data type that enable you to
record row creation and modification times automatically. These
properties are not available for other temporal types, although there
are reasons you might like them to be. For example, if you use
separate DATE
and TIME
columns to store record-modification
times, you can index the DATE
column to enable efficient date-based lookups. (With TIMESTAMP
, you cannot index just the date
part of the column.)
One way to simulate TIMESTAMP
properties for other temporal data types is to use the following
strategy:
When you create a row, initialize a DATE
column to the current date and a
TIME
column to the current
time.
When you update a row, set the DATE
and TIME
columns to the new date and
time.
However, this strategy requires all applications that use the table to implement the same strategy, and it fails if even one application neglects to do so. To place the burden of remembering to set the columns properly on the MySQL server and not on application writers, use triggers for the table. This is, in fact, a particular application of the general strategy discussed in Using a Trigger to Define Dynamic Default Column Values that uses triggers to provide calculated values for initializing (or updating) row columns.
The following example shows how to use triggers to simulate
TIMESTAMP
properties for each of
the DATE
, TIME
, and DATETIME
data types. Begin by creating the
following table, which has a nontemporal column for storing data and
columns for the DATE
, TIME
, and DATETIME
temporal types:
CREATE TABLE ts_emulate ( data CHAR(10), d DATE, t TIME, dt DATETIME );
The intent here is that applications will insert or update
values in the data
column, and
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 new row creation by
invoking the CURDATE()
,
CURTIME()
,
and NOW()
functions to get the current date, time, and date-and-time values 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(), NEW.dt = NOW();
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 values in the row actually change from their current
values:
CREATE TRIGGER bu_ts_emulate BEFORE UPDATE ON ts_emulate FOR EACH ROW BEGIN # update temporal columns only if the nontemporal column changes IF NEW.data <> OLD.data THEN SET NEW.d = CURDATE(), NEW.t = CURTIME(), NEW.dt = NOW(); END IF; END;
To test the INSERT
trigger,
create a couple of 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 | dt | +------+------------+----------+---------------------+ | cat | 2006-06-23 | 13:29:44 | 2006-06-23 13:29:44 | | dog | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +------+------------+----------+---------------------+
Change the data
value of one
row to verify that the BEFORE
UPDATE
trigger updates the temporal
columns of the changed row:
mysql>UPDATE ts_emulate SET data = 'axolotl' WHERE data = 'cat';
mysql>SELECT * FROM ts_emulate;
+---------+------------+----------+---------------------+ | data | d | t | dt | +---------+------------+----------+---------------------+ | axolotl | 2006-06-23 | 13:30:12 | 2006-06-23 13:30:12 | | dog | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +---------+------------+----------+---------------------+
Issue another UPDATE
, but
this time use one that does not change any data
column values. In this case, the
BEFORE
UPDATE
trigger should notice that no value
change occurred and leave the temporal columns unchanged:
mysql>UPDATE ts_emulate SET data = data;
mysql>SELECT * FROM ts_emulate;
+---------+------------+----------+---------------------+ | data | d | t | dt | +---------+------------+----------+---------------------+ | axolotl | 2006-06-23 | 13:30:12 | 2006-06-23 13:30:12 | | dog | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +---------+------------+----------+---------------------+
The preceding example shows how to simulate the
auto-initialization and auto-update properties offered by TIMESTAMP
columns. If you want only one of
those properties and not the other, create only one trigger and omit
the other.
3.16.66.156