MYSQL8触发器

MYSQL8触发器简介

触发器，就是一种特殊的存储过程。

触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。

在MySQL中，只有执行insert、delete、update（DML语言）操作时才能触发触发器的执行。

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器定义在一个表上，结构包含begin和end标识段，定义这个触发器的时候需要指定三个核心要素：

触发器的特性

操作-创建触发器

1、创建只有一个执行语句的触发器

CREATETRIGGER触发器名 before|after触发事件ON表名FOR EACH ROW执行语句;

2、创建有多个执行语句的触发器

createtrigger触发器名 before|after触发事件on表名for each rowbegin执行语句列表end;

数据准备

-- 创建数据库CREATEdatabaseifNOTEXISTSmydb10_trigger;-- 进入 数据库usemydb10_trigger;-- 创建用户表CREATETABLEUSER(uidintPRIMARYkey,usernamevarchar(50)NOTNULL,passwordvarchar(50)NOTNULL);-- 用户信息操作日志表CREATETABLEuser_logs(idintPRIMARYkeyauto_increment,timetimestamp,log_textvarchar(255));

Before与After区别：

在MySQL8中，触发器可以定义为在INSERT、UPDATE和DELETE（DML语言）语句执行之前（BEFORE）或之后（AFTER）自动执行的一组指令。

BEFORE触发器是在实际数据更改操作之前执行的。它们通常用于验证将要插入、更新或删除的数据是否符合特定的条件，以及在执行操作之前对其进行修改。BEFORE触发器还可以用于检查外键约束或确保数据的完整性。

AFTER触发器是在数据插入、更新或删除操作已经完成之后执行的。它们通常用于记录日志或执行其他操作，这些操作可能需要在数据进行更改后才能执行。AFTER触发器还可以用于计算汇总值或执行其他聚合操作。

因此，区别在于BEFORE触发器在执行实际数据更改操作之前执行，而AFTER触发器在执行完实际数据更改操作之后执行。

after：事件后执行，先完成业务增删改，然后再执行触发器语句，触发器语句晚于监视业务的增删改。

- `不能`对`new`进行修改。 - after insert - after update - after delete

before：事件前执行，先完成触发器语句，再进行业务增删改，触发语句先于监视业务的增删改。

- (insert、update)`可以`对`new`进行修改。 - before insert - before update - before delete

两者都不能修改old数据。

after插入数据后记录

需求：

需求1:当user表添加一行数据，则会自动在user_log表中添加日志记录

实现代码

after insert中的after关键字表示：
触发器在插入业务数据操作之后触发执行。也就是说，当有新数据行被插入到指定的业务数据表时，该触发器会在这些数据行被成功插入后立即执行。

on user指定了触发器所绑定的数据表，for each row表示对每一行数据都执行一次触发器操作。

-- 触发器CREATEtriggertrigger_test1afterinsertonuserfor each rowinsertintouser_logsvalues(null,now(),'有新用户添加');

before成功修改数据后记录

需求：

– 需求2: 当user表数据被修改时，则会自动在user_log添加日志记录

实现代码

before update，before表示在更新操作之前触发执行。

on user指定了触发器所绑定的数据表，for each row表示对每一行数据都执行一次触发器操作。

-- 需求2: 当user表数据被修改时，则会自动在user_log添加日志记录DELIMITER$$-- before update表示数据被成功修改后记录createtriggertrigger_test2 beforeupdate-- on user，表示绑定(坚实)user表-- for each row ,表示每次变更都执行记录onuserfor each rowBEGIN-- 当user表的数据被成功修改后，执行这段sql，往user_logs表中插入信息insertintouser_logsvalues(null,now(),'有用户信息被修改');END$$DELIMITER;

NEW和OLD

当在触发器中编写INSERT、UPDATE或DELETE语句时，“new” 和 “old” 分别代表触发器所针对的表中被修改（即被更新、插入或删除）的新行和旧行。

如果触发器是在INSERT操作之前被触发，那么 “new” 代表将要被插入到表中的新行。

如果是在UPDATE或DELETE操作之前被触发，那么 “old” 代表将要被更新或删除的行，而 “new” 代表更新后或者删除后将要被插入到表中的新行。

MYSQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容：具体如下：

使用方法：

NEW.相应数据表的列名

NEW示例：

需求：记录添加的数据

-- NEW-- after insert类型定义触发器：-- after事后记录CREATEtriggertrigger_test3afterinsertonuserfor each rowinsertintouser_logsvalues(null,now(),concat('有新用户添加，信息为：',NEW.uid,NEW.username,NEW.password));

OLD示例

需求：记录修改前信息

-- OLD-- 记录修改前的信息CREATEtriggertrigger_test4afterupdateonuserfor each rowinsertintouser_logsvalues(null,NOW(),concat('有用户信息被修改','信息修改前为：'OLD.uid,OLD.username.OLD.password));

需求：记录删除信息

-- 记录删除信息CREATEtriggertrigger_test6afterdeleteonuserfor each rowinsertintouser_logsvalues(null,NOW(),concat_ws(',','有用户被删除，被删除用户信息为：',OLD.uid,OLD.username,OLD.password));

查看触发器

-- 查看触发器showtriggers;

删除触发器

-- 删除触发器droptriggerifexists触发器名称;

MySQL触发器注意事项

1、MYSQL中触发器中不能对本表进行insert,update,delete操作，以免递归循环触发

2、尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。

3、触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。