Saturday, August 14, 2010

Using triggers to automatically set created and updated date fields in a mysql table

So your application uses a mysql database and you want to keep track of when rows were created and updated. You add a created and updated column and then redo all your insert and update queries: setting created and updated to now() for inserts, and setting updated to now() for updates. Commit the code, test, fix the spots where you spelled created craeted, test, commit, wash, rinse, guh. Oh, now you want to do this with the other dozen tables in your application. If only mysql could just do this for you. Well, obviously it can otherwise I wouldn't be writing this.

Triggers

I'm going to create a new table to show how to use triggers to achieve the goal of automatically time-stamping your rows on inserts and updates. Imagine if you will that the table looks something like the following:
mysql> create table demo (
-> id int(10) unsigned not null auto_increment,
-> test varchar(20) collate utf8_unicode_ci not null,
-> created datetime not null default '2000-01-01 00:00:00',
-> updated datetime not null default '2000-01-01 00:00:00',
-> primary key (id)
-> ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> describe demo;
+---------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| test | varchar(20) | NO | | NULL | |
| created | datetime | NO | | 2000-01-01 00:00:00 | |
| updated | datetime | NO | | 2000-01-01 00:00:00 | |
+---------+------------------+------+-----+---------------------+----------------+
4 rows in set (0.00 sec)

The default value I selected for the created and update columns is Y2K as this should be a pretty unreasonable date for any of your current and future updates. In theory should something break, mysql will use this default date and you'll be able to pick them out with ease. The created and updated columns don't need to prevent nulls for this to work, I chose this option as there is no conceivable reason for these columns to not be populated. I want an insert trigger and an update trigger so I use create trigger to make both ins_setdates and upd_setdates:
mysql> create trigger ins_setdates before insert on demo
-> for each row set new.created=now(), new.updated=now();
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger upd_setdates before update on demo
-> for each row set new.created=old.created, new.updated=now();
Query OK, 0 rows affected (0.01 sec)

mysql> show triggers;
+--------------+--------+-------+------------------------------------------------+--------+- :
| Trigger | Event | Table | Statement | Timing | :
+--------------+--------+-------+------------------------------------------------+--------+-[snip!]
| ins_setdates | INSERT | demo | set new.created=now(), new.updated=now() | BEFORE | >8
| upd_setdates | UPDATE | demo | set new.created=old.created, new.updated=now() | BEFORE | :
+--------------+--------+-------+------------------------------------------------+--------+- :
2 rows in set (0.00 sec)

(Note that the show triggers result was truncated as the table was 197 characters long and described the character sets (utf8) and collations (utf8_unicode_ci) and the pre would have splooged over everything)

The insert trigger, ins_setdates sets demo.created to now() and demo.updated to now() when you insert a row. The new prefix on new.created and new.updated tells mysql to set the new value of these fields.

The update trigger, upd_setdates sets demo.created to the value that it previously held (old.updated), and demo.updated to now().

Let us now see them in action. First we will insert a few rows. Note that I am not specifying values for either created or updated (or id, as this is an auto_increment field). This will be of serious use to you if you are adding these columns to an existing table and do not want your existing queries to break.
mysql> insert into demo set test='this is a test';
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo set test='that is not a moon';
Query OK, 1 row affected (0.00 sec)

mysql> insert into demo set test='1234567890';
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo;
+----+--------------------+---------------------+---------------------+
| id | test | created | updated |
+----+--------------------+---------------------+---------------------+
| 1 | this is a test | 2010-08-14 05:17:29 | 2010-14-08 05:17:29 |
| 2 | that is not a moon | 2010-08-14 05:17:40 | 2010-14-08 05:17:40 |
| 3 | 1234567890 | 2010-08-14 05:17:51 | 2010-14-08 05:17:51 |
+----+--------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

So each row was inserted with the current date and time. On the insert, I have chosen to put a time in for updated as (IMHO) an insert of data is the last time it was updated. Since updated == created, you know there have been no further updates since the insert. You could chose to insert a null (or have the default value null). Whatever, as long as you can tell from looking whether there have been updates. Both these fields currently show the time the row was inserted as expected based on the insert trigger.

Now let's update the data. It looks like I entered an incorrect quote, so I will update that:
mysql> update demo set test='that\'s no moon' where id=2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from demo;
+----+----------------+---------------------+---------------------+
| id | test | created | updated |
+----+----------------+---------------------+---------------------+
| 1 | this is a test | 2010-08-14 05:17:29 | 2010-08-14 05:17:29 |
| 2 | that's no moon | 2010-08-14 05:17:40 | 2010-08-14 05:18:13 |
| 3 | 1234567890 | 2010-08-14 05:17:51 | 2010-08-14 05:17:51 |
+----+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

As you can see, the update query updated not only the test field as I asked it to, but the trigger also set updated to the current date and time. The created field remained the same as before (check above if you don't believe me) as we didn't just create this row.

Now, let's say it's really important for my application to always know that the created field is and will always be the real time the row was created (say to prove I got my high score before you did). We would never want this field to change ever. If you remember the update trigger, it set the value of created to whatever was there before the update. So if we try to change created to April Fools day, our change won't happen (but updated will update -- so there is now a record that someone attempted an update):
mysql> update demo set created='2010-04-01 0:00' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from demo;
+----+----------------+---------------------+---------------------+
| id | test | created | updated |
+----+----------------+---------------------+---------------------+
| 1 | this is a test | 2010-08-14 05:17:29 | 2010-08-14 05:18:55 |
| 2 | that's no moon | 2010-08-14 05:17:40 | 2010-08-14 05:18:13 |
| 3 | 1234567890 | 2010-08-14 05:17:51 | 2010-08-14 05:17:51 |
+----+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

Booyakasha.

Triggers can get much more involved and complicated than this, but will (could) benefit your code by making it less involved and complicated. Your results may vary, but give them a try and have fun.