【MySQL系列】如何在MySQL中使用触发器？MySQL触发器详解

MySQL可以通过触发器来实现自动化业务逻辑和操作。触发器是一种在数据库表发生特定操作时自动执行的存储过程，能够响应特定事件，如INSERT、UPDATE和DELETE语句。

本文将详细介绍MySQL中的触发器概念、创建和使用方法，以及一些注意事项。

一、概念

触发器是一种与表相关联的一段代码，它会在特定事件（INSERT、UPDATE和DELETE语句）发生时自动执行。触发器可以在数据表中插入或更新数据的时候自动执行存储过程，从而实现约束、默认值或处理业务逻辑的功能。

触发器可以在MySQL中创建和删除，已经创建的触发器可以修改它的定义。每个触发器都有一个触发事件和响应事件，触发事件通常是数据表上的INSERT、UPDATE或DELETE语句，响应事件是在触发事件后MySQL服务器执行的操作。

二、MySQL触发器语法

1.常规触发器

常规触发器是MySQL触发器的基本类型，它可以在INSERT、UPDATE或DELETE语句被执行前或执行后自动触发。常规触发器的语法如下：

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW
BEGIN
	trigger_body;
END;

其中，`trigger_name`为触发器的名称，`table_name`为触发器所依附的表名，`trigger_body`为触发器的执行语句。另外，`BEFORE`和`AFTER`关键字表示触发器是否在数据库事件发生之前或之后被执行；`INSERT`、`UPDATE`和`DELETE`关键字则表示所触发的数据库事件。`FOR EACH ROW`则表示为每一行数据都执行该触发器。

下面是一个常规触发器的示例，它可以在每次往`test`表中插入数据之前打印出一行信息：

CREATE TRIGGER test_trigger
BEFORE INSERT
ON test FOR EACH ROW
BEGIN
	INSERT INTO log (message) VALUES ('New data added');
END;

2.批量触发器

批量触发器可以在查询语句执行前或执行后自动触发。与常规触发器不同的是，批量触发器可以同时处理多行数据，并且它通常被用于执行一些比较耗时的计算操作。批量触发器的语法如下：

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
BEGIN
	trigger_body;
END;

与常规触发器类似的是，`trigger_name`、`table_name`以及`trigger_body`的含义都相同；`BEFORE`和`AFTER`关键字也表示所触发的数据库事件。不同的是，批量触发器并不需要使用`FOR EACH ROW`关键字来限定行级触发器。

下面是一个批量触发器的示例，它可以在每次往`test`表中插入1000行数据后打印出一行信息：

CREATE TRIGGER test_trigger
AFTER INSERT
ON test
BEGIN
	IF (SELECT COUNT(*) FROM test) >= 1000 THEN
		INSERT INTO log (message) VALUES ('Batch data added');
	END IF;
END;

3.级联触发器

级联触发器属于高级的MySQL触发器类型，它可以在多个表之间自动触发。一个级联触发器通常会在表之间建立某种关联关系，比如外键约束，以便在父表中的数据被修改、删除或新增时，自动更新子表中的对应数据。级联触发器的语法如下：

CREATE TRIGGER trigger_name
AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
	trigger_body;
END;

其中，`trigger_name`、`table_name`和`trigger_body`的含义与常规触发器相同；`AFTER`关键字则表示级联触发器只能在数据表中修改、删除或新增之后触发。

下面是一个级联触发器的示例，它可以在父表`users`中的某个用户被删除时，删除子表`orders`中与该用户相关的订单信息：

CREATE TRIGGER del_order_trigger
AFTER DELETE
ON users
FOR EACH ROW
BEGIN
	DELETE FROM orders WHERE user_id=OLD.user_id;
END;

4.删除触发器

和删除表和存储过程类似，都是使用DROP语句来删除触发器。

#删除触发器T1
DROP TRIGGER IF EXISTS T1;

触发器不能够更新和修改，想要修改一个触发器的内容，只能先将触发器删除，在创建一个新的触发器。

5.查询触发器

#在已知数据库内，查询触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = '触发器名'
#查询所有的触发器
SELECT * FROM information_schema.TRIGGERS（WHERE TRIGGER_SCHEMA = '数据库名'）

 

6.触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

1. 如果 BEFORE 触发器执行失败，SQL 无法正确执行。
2. SQL 执行失败时，AFTER 型触发器不会触发。
3. AFTER 类型的触发器执行失败，SQL 会回滚

三、触发器的作用

触发器是基于行触发的，删除、新增或者修改操作可能都会激活触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，因此在设计触发器的时候一定要有所考虑，尽量不要编写过于复杂的触发器，也不要增加过多的触发器。

触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细、更复杂的数据控制能力。触发器主要有6个作用，具体如下：

1. 安全性：可以基于数据库使用户具有操作数据库的某种权利，可以基于时间限制用户的操作，还可以基于数据库中的数据限制用户的操作。
2. 审计：可以跟踪用户对数据库的操作，审计用户操作数据库的语句，把用户对数据库的更新写入审计表。
3. 实现复杂的数据完整性规则，实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象，还可以提供可变的默认值。
4. 实现复杂的非标准的数据库相关完整性规则：触发器可以对数据库中相关表进行连环更新。
5. 同步实时地复制表中的数据。
6. 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

 四、触发器优点和缺点

触发器是用户定义在关系表上的一类由事件触发的特殊过程。一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。触发器类似于约束，但是比约束灵活，具有更强大的数据控制能力。

触发器的优点如下：

1. 自动执行：触发器在操作表数据时立即被激活。
2. 级联更新：触发器可以通过数据库中的相关表进行层叠更改。
3. 强化约束：触发器可以引用其他表中的列，能够实现比CHECK约束更复杂的约束。
4. 跟踪变化：触发器可以阻止数据库中未经许可的指定更新和变化。
5. 强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。

MySQL是一个非常强大的关系型数据库管理系统，为了更好地维护和管理数据，往往需要使用触发器。但是，有许多原因使得MySQL不适合使用触发器：

1. 性能问题：MySQL的触发器通常会影响数据库的性能，尤其是在大规模数据处理时。
2. 困难的调试：触发器通常会给开发人员带来很多麻烦，比如调试是一件非常困难的事情。
3. 加重负担：使用触发器往往会增加数据库的负载，当触发器数量增加时，负担也会增加。
4. 安全性问题：触发器不能很好地保证数据的安全性，因为它们可能会被黑客攻击，导致数据泄露和损坏。