Tutorial Trigger in Mysql with Example

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>
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!