MySQL触发器什么是触发器

触发器（trigger）是 MySQL 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。简单理解为：你执行一条 sql 语句，这条 sql 语句的执行会自动去触发执行其他的 sql 语句。

触发器的作用可在写入数据表前，强制检验或转换数据。触发器发生错误时，异动的结果会被撤销。部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为 DDL 触发器。可依照特定的情况，替换异动的指令 (INSTEAD OF)。触发器创建的四要素监视地点（table）监视事件（insert、update、delete）触发时间（after、before）触发事件（insert、update、delete）触发器的使用语法语法

CREATE TRIGGER 触发器名BEFORE|AFTER DELETE|INSERT|UPDATEON 表名 FOR EACH ROWBEGIN触发SQL代码块;END;

参数

描述

before/after

触发器是在增删改之前执行，还是之后执行

delete/insert/update

触发器由哪些行为触发（增、删、改）

on 表名

触发器监视哪张表的（增、删、改）操作

触发SQL代码块

执行触发器包含的 SQL 语句

说明

触发器也是存储过程程序的一种，而触发器内部的执行 SQL 语句是可以多行操作的，所以在 MySQL 的存储过程程序中，要定义结束符。

查看触发器语法

SHOW TRIGGERS\G;

结果，显示所有触发器的基本信息；无法查询指定的触发器。在 information_schema.triggers 表中查看触发器信息：

mysql> SELECT * FROM information_schema.triggers\G

比如：

mysql> select * from information_schema.triggers where trigger_name='upd_check'\G;

所有触发器信息都存储在 information_schema 数据库下的 triggers 表中，可以使用 SELECT 语句查询，如果触发器信息过多，最好通过 TRIGGER_NAME 字段指定查询。

删除触发器语法

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

删除触发器之后最好使用上面的方法查看一遍；同时，也可以使用 database.trig 来指定某个数据库中的触发器。如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。

案例创建BEFORE类型触发器

在 test_db 数据库中，数据表 tb_emp8 为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp8 的表结构如下所示：

mysql> SELECT * FROM tb_emp8;Empty set (0.07 sec)mysql> DESC tb_emp8;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id     | int(11)     | NO   | PRI | NULL    |       || name   | varchar(22) | YES  | UNI | NULL    |       || deptId | int(11)     | NO   | MUL | NULL    |       || salary | float       | YES  |     | 0       |       |+--------+-------------+------+-----+---------+-------+4 rows in set (0.05 sec)

创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。输入的 SQL 语句和执行过程如下所示：

mysql> CREATE TRIGGER SumOfSalary    -> BEFORE INSERT ON tb_emp8    -> FOR EACH ROW    -> SET @sum=@sum+NEW.salary;Query OK, 0 rows affected (0.35 sec)

触发器 SumOfSalary 创建完成之后，向表 tb_emp8 中插入记录时，定义的 sum 值由 0 变成了 1500，即插入值 1000 和 500 的和，如下所示：

SET @sum=0;Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO tb_emp8    -> VALUES(1,'A',1,1000),(2,'B',1,500);Query OK, 2 rows affected (0.09 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT @sum;+------+| @sum |+------+| 1500 |+------+1 row in set (0.03 sec)

在 test_db 数据库中，数据表 tb_emp6 和 tb_emp7 都为员工信息表，包含 id、name、deptId 和 salary 字段，数据表 tb_emp6 和 tb_emp7 的表结构如下所示：

mysql> SELECT * FROM tb_emp6;Empty set (0.07 sec)mysql> SELECT * FROM tb_emp7;Empty set (0.03 sec)mysql> DESC tb_emp6;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id     | int(11)     | NO   | PRI | NULL    |       || name   | varchar(25) | YES  |     | NULL    |       || deptId | int(11)     | YES  | MUL | NULL    |       || salary | float       | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> DESC tb_emp7;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id     | int(11)     | NO   | PRI | NULL    |       || name   | varchar(25) | YES  |     | NULL    |       || deptId | int(11)     | YES  |     | NULL    |       || salary | float       | YES  |     | 0       |       |+--------+-------------+------+-----+---------+-------+4 rows in set (0.04 sec)

创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。输入的 SQL 语句和执行过程如下所示：

mysql> CREATE TRIGGER double_salary    -> AFTER INSERT ON tb_emp6    -> FOR EACH ROW    -> INSERT INTO tb_emp7    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);Query OK, 0 rows affected (0.25 sec)

触发器 double_salary 创建完成之后，向表 tb_emp6 中插入记录时，同时向表 tb_emp7 中插入相同的记录，并且 salary 字段为 tb_emp6 中 salary 字段值的 2 倍，如下所示：

mysql> INSERT INTO tb_emp6    -> VALUES (1,'A',1,1000),(2,'B',1,500);Query OK, 2 rows affected (0.09 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM tb_emp6;+----+------+--------+--------+| id | name | deptId | salary |+----+------+--------+--------+|  1 | A    |      1 |   1000 ||  2 | B    |      1 |    500 |+----+------+--------+--------+3 rows in set (0.04 sec)mysql> SELECT * FROM tb_emp7;+----+------+--------+--------+| id | name | deptId | salary |+----+------+--------+--------+|  1 | A    |      1 |   2000 ||  2 | B    |      1 |   1000 |+----+------+--------+--------+2 rows in set (0.06 sec)