触发器的简单使用

Leave a reply

1、创建触发器

  • 修改界定符：delimiter //
  • 定义触发器：create trigger xxx after|before insert|update|delete on xxxx for each row
  • 触发器执行内容：begin xxx end
  • //执行
  • 还原界定符：delimiter ;
mysql> create trigger t_ststis after insert on user for each row
    -> begin
    -> update t_statistics set u_count = u_count + 1 where id = 1;
    -> end //
Query OK, 0 rows affected (0.01 sec)


mysql> delimiter ;
mysql> select * from t_statistics;
+----+---------+
| id | u_count |
+----+---------+
|  1 |       0 |
+----+---------+
1 row in set (0.00 sec)


mysql> insert into user(user_name, password, email) values('lucy', md5(2), 'lucy@163.com');
Query OK, 1 row affected (0.00 sec)


mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
|  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 15:57:29 |
|  2 | 余西安    | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    |    1.00 |  33 | 2019-04-21 17:12:55 |
|  3 | Jack      | 男  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |
|  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
|  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
|  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
|  7 | lucy      | 男  | c81e728d9d4c2f636f067f89cc14862c | lucy@163.com    | NULL    |    0.00 |   0 | 2019-04-23 20:13:01 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
7 rows in set (0.00 sec)


mysql> select * from t_statistics;
+----+---------+
| id | u_count |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

Leave a Reply