嗨客网搜索
MySQL触发器

什么是触发器

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

触发器的作用

  1. 可在写入数据表前,强制检验或转换数据。
  2. 触发器发生错误时,异动的结果会被撤销。
  3. 部分数据库管理系统可以针对数据定义语言(DDL)使用触发器,称为 DDL 触发器。
  4. 可依照特定的情况,替换异动的指令 (INSTEAD OF)。

触发器创建的四要素

  1. 监视地点(table)
  2. 监视事件(insert、update、delete)
  3. 触发时间(after、before)
  4. 触发事件(insert、update、delete)

触发器的使用语法

语法

CREATE TRIGGER 触发器名 BEFORE|AFTER DELETE|INSERT|UPDATE ON 表名 FOR EACH ROW BEGIN 触发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: 0 mysql> SELECT @sum; +------+ | @sum | +------+ | 1500 | +------+ 1 row in set (0.03 sec)

创建AFTER类型触发器

在 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: 0 mysql> 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)
嗨客网顶部