MySql触发器实现统计文章数

创建触发器的sql语句为：
create trigger trigger_name after/before insrt/update/delete on table_name for each row;
本文中有两个表， 分别命名为 article和article_number.
实现的是在表article中每添加一篇文章，article_numeber表中 的数目自动加一，每删除一篇文章， article_number 的数目自动减一。
创建article：
mysql> create table article(
-> id int unsigned not null auto_increment primary key,
-> text tinyint unsigned not null
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc article;
+——-+———————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+———————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| text | tinyint(3) unsigned | NO | | NULL | |
+——-+———————+——+—–+———+—————-+
2 rows in set (0.00 sec)

创建表article_number:
mysql> create table article_number(
-> id int unsigned not null auto_increment primary key,
-> totals int unsigned not null
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc article_number;
+——–+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——–+——————+——+—–+———+—————-+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| totals | int(10) unsigned | NO | | NULL | |
+——–+——————+——+—–+———+—————-+
2 rows in set (0.00 sec)

初始化表article_number:
mysql> insert into article_number (totals) values(0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 0 |
+—-+——–+

创建insert触发器：
mysql> delimiter //
mysql> create trigger insert_trigger after insert on article for each row
-> begin
-> update article_number set totals=totals+1 where id=1;
-> end //
Query OK, 0 rows affected (0.02 sec)

测试添加文章：
mysql> insert into article (text) values(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 1 |
+—-+——–+
mysql> insert into article (text) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 2 |
+—-+——–+

insert触发器创建成功， article表中每增加一篇文章， article_number 中的totals都会增加1.

接下来创建delete 触发器：
mysql> delimiter //
mysql> create trigger delete_trigger after delete on article for each row
-> begin
-> update article_number set totals=totals-1 where id=1;
-> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

测试delete
mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 3 |
+—-+——–+
1 row in set (0.00 sec)

mysql> delete from article where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 2 |
+—-+——–+
1 row in set (0.00 sec)
mysql> delete from article where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from article_number;
+—-+——–+
| id | totals |
+—-+——–+
| 1 | 1 |
+—-+——–+
1 row in set (0.00 sec)

至此，添加删除文章的触发器创建成功。