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

╰+攻爆jí腚メ 2024-03-03 08:55 74阅读 0赞

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.  安全性问题：触发器不能很好地保证数据的安全性，因为它们可能会被黑客攻击，导致数据泄露和损坏。