MySQL triggers

Now that even my favourite $50/year web hosts (example) are offering MySQL 5 and even recommending it over 4, why not take advantage of MySQL's "enterprise" features. Like triggers. OK, what's a trigger? It's something that happens as a result of something else. Clear? Yeah, maybe an example.

Say, as it often happens in life, you insert a row into a table. If there is a trigger that "listens" to inserts on this table, it will be, well... triggered.

Quick example. You have a blog app and a `blog` database that has a table called `posts`. You want to show some stats, like the total number of posts. Instead of using some (potentially heavy) queries that include some COUNT()-ing and SUM()-ing, you can "cache" the stats for faster display. So you create a table called `stats`. Let's see.

C:\\Development\\Apache\\MySQL\\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 240 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \u blog
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_blog |
+----------------+
| posts          |
| stats          |
+----------------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM posts;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(255)     | NO   |     |         |                |
| body  | text             | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM stats;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| key   | varchar(50) | NO   | PRI |         |       |
| value | int(11)     | NO   |     |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

So far, so good. Initializing the stats...

mysql> INSERT INTO stats VALUES ('posts', 0);
Query OK, 1 row affected (0.02 sec)

Let's create the trigger!

mysql> CREATE TRIGGER mytrigger
    -> AFTER INSERT ON posts
    -> FOR EACH ROW
    -> UPDATE stats SET `value` = `value` + 1 WHERE `key` = 'posts';
Query OK, 0 rows affected (0.01 sec)

What does it mean? Well, after each row INSERT-ed in `posts`, it will update the `stats` table, incrementing the total number of posts. Does it work? But of course!

mysql> INSERT INTO posts VALUES ('', 'title1', 'body1');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> INSERT INTO posts VALUES ('', 'title2', 'body2');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SELECT * FROM stats;
+-------+-------+
| key   | value |
+-------+-------+
| posts |     2 |
+-------+-------+
1 row in set (0.00 sec)

mysql> ha-haaaa!

To display the triggers you've created, use the SHOW TRIGGERS statement, as described in the MySQL manual. More on the triggers - using triggers, CREATE TRIGGER syntax

Tell your friends about this post on Facebook and Twitter