(资料图片仅供参考)

一、触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的sql语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

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

二、触发器类型insert型触发器：new表示将要或者已经新增的数据update型触发器：old表示修改之前的数据，new表示将要或已经修改后的数据delete型触发器：old表示将要或者已经删除的数据三、触发器语法　　3.1 创建语法

create trigger trigger_namebefore/after insert/update/deleteon tb_name for each rowbegin　　trigger_stmt;end;

3.2 查看语法

show triggers;

3.3 删除语句

drop trigger [schema_name.] trigger_name;  -- 如果没有指定schema_name，默认为当前数据库

四、插入数据触发器案例　　4.1 需求：

通过触发器记录student表的数据插入时，将变更日志插入到日志表student_logs中；

4.2 创建 student_logs表

mysql> create table student_logs(    -> id int primary key auto_increment,    -> operation varchar(20) comment "操作类型,insert/update/delete",    -> operate_time datetime comment "操作时间",    -> operate_id int comment "操作的ID",    -> operate_params varchar(500) comment "操作参数"    -> )comment "student操作日志表";Query OK, 0 rows affected (1.18 sec)

4.3 创建插入数据触发器

mysql>delimiter &mysql> create trigger student_insert_trigger        -> after insert on student for each row         ->begin         ->    insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null, "insert", now(), new.id, concat("插入的数据内容为：id=",new.id,"name=",new.name,"age=",new.age,"score=",new.score));        ->end;        ->&Query OK, 0 rows affected (0.20 sec)   

说明1：delimiter &在之前的文章中已经提到过，是将mysql中的语句终止符由“；” 改为“&”,原因是，触发器中有完整的sql语句，会包含分号，如果不改，怎无法在终端中书写完整的触发器语句

4.4 查看创建的触发器

mysql> delimiter ;mysql> show triggers;+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| Trigger                | Event  | Table   | Statement                                                                                                                                                                                                                           | Timing | Created                | sql_mode                                                                                                              | Definer                           | character_set_client | collation_connection | Database Collation |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| student_insert_trigger | INSERT | student | begin insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null, "insert", now(), new.id, concat("插入的数据内容为：id=",new.id,"name=",new.name,"age=",new.age,"score=",new.score)); end          | AFTER  | 2023-08-16 00:07:46.53 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+1 row in set (0.00 sec)

说明2：delimiter是将sql语句终止符由“&”在改为“；”

4.5 插入验证

mysql> insert into student(id,name,age,score) values(null,"李白",30,100);Query OK, 1 row affected (0.21 sec)mysql> select * from student_logs;+----+-----------+---------------------+------------+-----------------------------------------------------------+| id | operation | operate_time        | operate_id | operate_params                                            |+----+-----------+---------------------+------------+-----------------------------------------------------------+|  1 | insert    | 2023-08-16 00:21:47 |          5 | 插入的数据内容为：id=5name=李白age=30score=100            |+----+-----------+---------------------+------------+-----------------------------------------------------------+1 row in set (0.00 sec)

说明3：插入数据李白的信息就已经自动的在student_logs中记录

五、修改数据触发器案例　　5.1 创建修改数据触发器

mysql>delimiter &mysql> create trigger student_update_trigger after update on student for each row begin insert into student_logs(id, operation, operate_time,operate_id,operate_params) values (null,"update",now(),new_id,concat( "更新前数据：id=",old.id,",name=",old.name,",age=",old.age,",score=",old.score," | 更新后数据：id=",new.id, ",name=",new.name, ",age=",new.age, ",score=",new.score)); end; &Query OK, 0 rows affected (1.86 sec)mysql> delimiter ;

5.2 查看创建的触发器

mysql> show triggers;+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| Trigger                | Event  | Table   | Statement                                                                                                                                                                                                                                                                                                               | Timing | Created                | sql_mode                                                                                                              | Definer                           | character_set_client | collation_connection | Database Collation |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| student_insert_trigger | INSERT | student | begin insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null, "insert", now(), new.id, concat("插入的数据内容为：id=",new.id,"name=",new.name,"age=",new.age,"score=",new.score)); end                                                                                              | AFTER  | 2023-08-16 00:07:46.53 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci || student_update_trigger | UPDATE | student | begin insert into student_logs(id, operation, operate_time,operate_id,operate_params) values (null,"update",now(),new.id,concat( "更新前数据：id=",old.id,",name=",old.name,",age=",old.age,",score=",old.score," | 更新后数据：id=",new.id, ",name=",new.name, ",age=",new.age, ",score=",new.score)); end             | AFTER  | 2023-08-16 23:38:26.36 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+2 rows in set (1.16 sec)

5.3 查看student表原始数据

mysql> select * from student;+----+--------+------+-------+| id | name   | age  | score |+----+--------+------+-------+|  1 | 张三   |   18 |    88 ||  2 | 李四   |   21 |    83 ||  3 | 王五   |   24 |    76 ||  4 | 赵六   |   19 |    94 ||  5 | 李白   |   30 |   100 |+----+--------+------+-------+5 rows in set (1.30 sec)

5.4修改student表，验证触发器

mysql> update student set age=25 where id = 5;Query OK, 1 row affected (0.38 sec)Rows matched: 1  Changed: 1  Warnings: 0

5.5 查看修改后的student表和student_logs表，验证触发器

mysql> select * from student;+----+--------+------+-------+| id | name   | age  | score |+----+--------+------+-------+|  1 | 张三   |   18 |    88 ||  2 | 李四   |   21 |    83 ||  3 | 王五   |   24 |    76 ||  4 | 赵六   |   19 |    94 ||  5 | 李白   |   25 |   100 |+----+--------+------+-------+5 rows in set (0.00 sec)mysql> select * from student_logs;+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+| id | operation | operate_time        | operate_id | operate_params                                                                                            |+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+|  1 | insert    | 2023-08-16 00:21:47 |          5 | 插入的数据内容为：id=5name=李白age=30score=100                                                            ||  2 | update    | 2023-08-16 23:58:23 |          5 | 更新前数据：id=5,name=李白,age=30,score=100 | 更新后数据：id=5,name=李白,age=25,score=100                 |+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

六、删除数据触发器案例　　6.1 创建删除数据触发器

mysql>delimiter &mysql> create trigger student_delete_trigger after delete on student for each row begin insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null,"delete",now(),old.id, concat("删除之前的数据：id=",old.id, ",name=",old.name,",age=",old.age,",score=",old.score)); end;&Query OK, 0 rows affected (0.87 sec)

6.2 查看触发器

mysql> show triggers;    -> &+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| Trigger                | Event  | Table   | Statement                                                                                                                                                                                                                                                                                                               | Timing | Created                | sql_mode                                                                                                              | Definer                           | character_set_client | collation_connection | Database Collation |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+| student_insert_trigger | INSERT | student | begin insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null, "insert", now(), new.id, concat("插入的数据内容为：id=",new.id,"name=",new.name,"age=",new.age,"score=",new.score)); end                                                                                              | AFTER  | 2023-08-16 00:07:46.53 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci || student_update_trigger | UPDATE | student | begin insert into student_logs(id, operation, operate_time,operate_id,operate_params) values (null,"update",now(),new.id,concat( "更新前数据：id=",old.id,",name=",old.name,",age=",old.age,",score=",old.score," | 更新后数据：id=",new.id, ",name=",new.name, ",age=",new.age, ",score=",new.score)); end             | AFTER  | 2023-08-16 23:57:34.20 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci || student_delete_trigger | DELETE | student | begin insert into student_logs(id,operation,operate_time,operate_id,operate_params) values (null,"delete",now(),old.id, concat("删除之前的数据：id=",old.id, ",name=",old.name,",age=",old.age,",score=",old.score)); end                                                                                               | AFTER  | 2023-08-17 00:15:46.44 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | skip-grants user@skip-grants host | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |+------------------------+--------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------+----------------------+----------------------+--------------------+3 rows in set (0.00 sec)

6.3 删除数据

mysql> delimiter ;mysql> delete from student where id = 5;Query OK, 1 row affected (0.70 sec)

6.4 查看student表和student_logs表验证触发器

mysql> select * from student;+----+--------+------+-------+| id | name   | age  | score |+----+--------+------+-------+|  1 | 张三   |   18 |    88 ||  2 | 李四   |   21 |    83 ||  3 | 王五   |   24 |    76 ||  4 | 赵六   |   19 |    94 |+----+--------+------+-------+4 rows in set (0.00 sec)mysql> select * from student_logs;+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+| id | operation | operate_time        | operate_id | operate_params                                                                                            |+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+|  1 | insert    | 2023-08-16 00:21:47 |          5 | 插入的数据内容为：id=5name=李白age=30score=100                                                            ||  2 | update    | 2023-08-16 23:58:23 |          5 | 更新前数据：id=5,name=李白,age=30,score=100 | 更新后数据：id=5,name=李白,age=25,score=100                 ||  3 | delete    | 2023-08-17 00:18:06 |          5 | 删除之前的数据：id=5,name=李白,age=25,score=100                                                           |+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)

关键词：