【转】MySql触发器trigger的使用

转载地址：https://itbilu.com/database/mysql/NyAc3Mdu.html

触发器（trigger）是与表事件相关的数据库对象，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发触发器的执行。比如，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 本文通过一个示例，介绍MySql触发器的使用方法。

1. MySql触发器语法

CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } 
ON <表名称>
FOR EACH ROW
<触发的SQL语句>

• 触发器名称 -- 触发器名字，最多64个字符，其命令规则和MySQL中其他对象的命名方式类似
• { BEFORE | AFTER } -- 触发器执行时间：可以设置为事件发生前或后
• { INSERT | UPDATE | DELETE } -- 触发事件：可以设置为在执行INSERT、UPDATE、DELETE操作时触发
• 表名称 -- 触发器所属表：触发器属于某一个表，当在这个表上执行INSERT、UPDATE、DELETE操作的时就会使触发器触发，一张表的同一个事件只能有一个触发器
• FOR EACH ROW -- 触发器的执行间隔：FOR EACH ROW子句通知触发器，每行执行一次动作
• 触发的SQL语句 -- 事件触发时所要执行的SQL语句

2. 触发器查看与删除

2.1 查看MySql触发器

在MySql中，查看已创建触发器和查看数据库SHOW DATABASES、查看表SHOW TABLES一样。查看触发器的语法如下：

SHOW TRIGGERS [FROM schema_name];

其中可选参数schema_name即Schema的名称，在MySQL中Schema和Database相关，查看时可以指定数据库名。指定后会从指定的数据库查询，不定则从USE database_name;切换的，当前工作数据库查询。

2.2 删除MySql触发器

在MySql中，删除已创建触发器和删除数据库、删除表一样，删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

3. MySql触发器使用示例

3.1 创建触发器

现有用户表users和用户角色关系表userRoles，其结构如下：

mysql> show columns from users;
+--------------------+---------------+------+-----+---------------------+----------------+
| Field              | Type          | Null | Key | Default             | Extra          |
+--------------------+---------------+------+-----+---------------------+----------------+
| userId             | bigint(11)    | NO   | PRI | NULL                | auto_increment |
| userName           | varchar(255)  | NO   |     | NULL                |                |
| password           | varchar(255)  | NO   |     | NULL                |                |
| name               | varchar(255)  | NO   |     | NULL                |                |
| nickName           | varchar(255)  | YES  |     | NULL                |                |
| avatar             | varchar(1024) | YES  |     | NULL                |                |
| sex                | int(11)       | NO   |     | 0                   |                |
| email              | varchar(255)  | NO   |     | NULL                |                |
| isManager          | tinyint(1     | NO   |     | NULL                |                |
| createdOn          | datetime      | NO   |     | 2014-05-06 11:15:25 |                |
| updatedOn          | datetime      | NO   |     | 2014-05-06 11:15:25 |                |
+--------------------+---------------+------+-----+---------------------+----------------+
21 rows in set (0.02 sec)

mysql> show columns from userRoles;
+--------+------------+------+-----+---------+----------------+
| Field  | Type       | Null | Key | Default | Extra          |
+--------+------------+------+-----+---------+----------------+
| id     | int(11)    | NO   | PRI | NULL    | auto_increment |
| userId | bigint(11) | YES  | MUL | NULL    |                |
| roleId | bigint(11) | YES  | MUL | NULL    |                |
+--------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

创建一个触发器t_after_insert_userRoles，在userRoles表插入数据，且roleId为1时，将users表相同userId的isManager字段更新为true。触发器创建过程如下：

mysql> DELIMITER $   //将SQL语句的结束符设置为#
mysql> CREATE TRIGGER t_after_insert_userRoles
AFTER INSERT ON
userRoles
FOR EACH ROW
BEGIN
    update users set isManager = 1 where userId = new.userId and new.roleId = 1;
END$

mysql> DELIMITER ;   //将SQL语句的结束符设重置为;以不影响后续使用

触发器创建完成。

3.2 验证触发器

向用户表users插入数据。

mysql> INSERT INTO users (userName, password, name, nickName, sex, email, isManager) VALUE ('itbilu', 'e10adc3949ba59abbe56e057f20f883e', 'IT笔录', 'itbilu', 1, 'cn.liuht@gmail.com', 0);

users原来没有数据，刚插入的数据userId为1。向用户表角色表userRoles插入数据，使触发器触发：

mysql> INSERT INTO userRoles (userId, roleId) VALUE (1, 1);

刚才插入的数据已经使触发器触发，查看结果如下：

mysql> select isManager from users;
+-----------+
| isManager |
+-----------+
|         1 |
+-----------+