phpied.com

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

This entry was posted on Friday, December 8th, 2006 and is filed under mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Get notification for future posts: follow me on Twitter or subscribe to my RSS feed