mysql trigger触发器简述

简介

触发器属于mysql的一个高级特性，它可以算是一个简化版的存储过程。与真正的存储过程不同的是，它不是被call调用的，而是在检测到满足条件的操作后自动执行的，类似于系统监控与告警的关系。

mysql的触发器只支持基于行的触发，始终都是针对于1条记录的。mysql的触发器是针对于行的insert\update\delete操作来触发，具体由哪个操作触发由用户的实际定义来决定。但每个表的每一个事件，最多只能定义一个触发器。

mysql的触发器触发时间分为两种：before和after，即触发事件执行之前或之后。例如before insert就表示在插入操作之前触发。

触发器相对来说有优点，它可以减少客户端和服务器之间的通信，从而可以提高性能；它可以对前台掩盖服务器背后的具体工作，能够加强数据的保密性；它也可以保证数据的一致性，当然，仅限于操作innodb表，innodb表上的触发器是在同一个事务中完成的。

有优点同样就会有缺点，因为隐藏了很多具体的工作，触发器出现问题就会更加难以排查；如果不清楚服务器端有触发器的操作，那几乎是无法定位到问题所在。

综合考虑系统中使用触发器的利与弊，然后决定是否在自已的系统中使用触发器。

语法规则

create trigger [trigger_name] [trigger_time] [trigger_event] on [table_name] for each row [trigger_operation]

trigger_name ：所创建的触发器的名称；
trigger_time ：触发器的执行时间；
trigger_event：触发器的依赖触发事件；
table_name：触发器所作用的表；
trigger_operation：具体的触发器操作语句；

示例

创建两个表，一个t_trigger_user用来保存人员信息，一个t_trigger_salary用来保存人员的薪水信息。两个空表用来测试，具体表结构如下：

mysql> show create table t_trigger_user\G*************************** 1. row ***************************       Table: t_trigger_userCreate Table: CREATE TABLE `t_trigger_user` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(32) DEFAULT '',  `age` int(3) unsigned DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> show create table t_trigger_salary\G*************************** 1. row ***************************       Table: t_trigger_salaryCreate Table: CREATE TABLE `t_trigger_salary` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `salary` int(10) unsigned DEFAULT '0',  `uid` int(10) NOT NULL,  `name` varchar(32) DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

测试insert操作触发器

首先创建一个基于t_trigger_user表的insert操作的触发器（我们默认所有的操作都在具体操作之后触发），这个触发器用来完成”当t_trigger_user表中的新的数据插入时，同步在t_trigger_salary表中插入一行相关信息“。
创建after insert触发器

mysql> create trigger user_insert after insert on t_trigger_user for each row insert into t_trigger_salary(uid,name) values(new.id,new.name);

向触发器所在表t_trigger_user中执行insert操作，然后查看两个表中信息

mysql> insert into t_trigger_user(name,age) values('lily',21);                                                                           Query OK, 1 row affected (0.01 sec)mysql> select * from t_trigger_user;+----+------+------+| id | name | age  |+----+------+------+|  2 | lily |   21 |+----+------+------+1 row in set (0.00 sec)mysql> select * from t_trigger_salary;+----+--------+-----+------+| id | salary | uid | name |+----+--------+-----+------+|  1 |      0 |   2 | lily |+----+--------+-----+------+1 row in set (0.00 sec)

可以看到，t_trigger_user表在创建了insert操作触发器之后，在执行了insert操作后，数据插入成功了，同样触发了触发器执行，向t_trigger_salary表中也插入了一条数据。

同样的道理，再来测试一下update操作和delete操作的触发器实现。

update操作触发器示例

mysql> delimiter //mysql> create trigger update_user after update on t_trigger_user for each row    -> begin    -> if new.name != old.name    -> then    -> update t_trigger_salary set name=new.name where uid=old.id;    -> end if;    -> end    -> //Query OK, 0 rows affected (0.00 sec)mysql> update t_trigger_user set name='anvil' where id=2;    -> //Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> delimiter ;mysql> select * from t_trigger_user;+----+-------+------+| id | name  | age  |+----+-------+------+|  2 | anvil |   21 |+----+-------+------+1 row in set (0.00 sec)mysql> select * from t_trigger_salary;+----+--------+-----+-------+| id | salary | uid | name  |+----+--------+-----+-------+|  1 |      0 |   2 | anvil |+----+--------+-----+-------+1 row in set (0.00 sec)

delete操作触发器示例

mysql> create trigger delete_user after delete on t_trigger_user for each row delete from t_trigger_salary where uid=old.id;Query OK, 0 rows affected (0.01 sec)mysql> delete from t_trigger_user where id=2;Query OK, 1 row affected (0.00 sec)mysql> select * from t_trigger_user;Empty set (0.00 sec)mysql> select * from t_trigger_salary;Empty set (0.00 sec)

是的，触发器中的[trigger_operation]不仅仅支持单条的sql操作语句，也支持通过begin….end包起来的一连串操作语句。

当触发器被触发，然后发生错误的时候，触发器中的操作不会被执行，同样的，触发了这个触发器的操作语句也不会被实际执行。