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