MySQL-触发器（TRIGGER）

文章目录

1. 触发器是什么？

• 定义：MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（ INSERT， DELETE 或 UPDATE）时就会激活它执行。
• 作用：触发器与数据表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性、日志记录 , 数据校验等操作。

2. 触发器的优缺点

触发器的优点如下：

• 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
• 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
• 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

触发器的缺点如下：

• 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
• 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，
• 如果需要变动的数据量较大时，触发器的执行效率会非常低。

3. 触发器的类型

• 在实际使用中， MySQL 所支持的触发器有三种： INSERT 触发器、 UPDATE 触发器和 DELETE 触发器。
• 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。触发器类型NEW 和 OLDINSERT 型触发器NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据

3.1 INSERT触发器

• 在 INSERT 语句执行之前或之后响应的触发器，使用 INSERT 触发器需要注意以下几点：1. 更多操作在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。2. 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。3. 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

3.2 UPDATE触发器

• 在 UPDATE 语句执行之前或之后响应的触发器，使用 UPDATE 触发器需要注意以下几点：1. 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。2. 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。3. 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。4. OLD 中的值全部是只读的，不能被更新。注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

3.3 DELETE触发器

• DELETE 语句执行之前或之后响应的触发器，使用 DELETE 触发器需要注意以下几点：1. 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。2. OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

1. 对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。
2. 若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。
3. 若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。
4. 仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

4. 触发器的相关语法

4.1 创建触发器

• 在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器，语法格式如下：CREATE <触发器名> < BEFORE | AFTER ><INSERT | UPDATE | DELETE >ON <表名> FOR EACH Row<触发器主体>

1. 触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

1. INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。

注意：三种触发器的执行时间如下。

• INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
• DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
• UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

1. BEFORE | AFTER

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

1. 表名

与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

1. 触发器主体

触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

1. FOR EACH ROW

一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

• 案例1：插入数据触发器create trigger tb_user_insert_triggerafter insert on tb_user for each rowbegin insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',profession=', NEW.profession));end;
• 案例2：修改触发器create trigger tb_user_update_triggerafter update on tb_user for each rowbegin insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'update', now(), new.id,concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));end;
• 案例3：删除触发器create trigger tb_user_delete_triggerafter delete on tb_user for each rowbegin insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES (null, 'delete', now(), old.id,concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));end;

4.2 查看触发器

• 在 MySQL 中，可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息，语法格式如下：SHOW TRIGGERS;
• 在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看，具体的语法如下：SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
• 查看所有的触发器:SELECT * FROM information_schema.triggers

4.3 删除触发器

• 使用 DROP TRIGGER 语句可以删除 MySQL 中已经定义的触发器。DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>