MySql触发器trigger的使用

 2015年07月10日    2314     声明


触发器(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_nameSchema的名称,在MySQL中SchemaDatabase相关,查看时可以指定数据库名。指定后会从指定的数据库查询,不定则从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表插入数据,且roleId1时,将users表相同userIdisManager字段更新为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原来没有数据,刚插入的数据userId1。向用户表角色表userRoles插入数据,使触发器触发:

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

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

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