MySQL高级8-触发器
一、触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的sql语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发。
二、触发器类型
- insert型触发器：new表示将要或者已经新增的数据
- update型触发器：old表示修改之前的数据，new表示将要或已经修改后的数据
- delete型触发器：old表示将要或者已经删除的数据
三、触发器语法
3.1 创建语法
create trigger trigger_name before/after insert/update/delete on tb_name for each row begin 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)
侯哥语录：我曾经是一个职业教育者，现在是一个自由开发者。我希望我的分享可以和更多人一起进步。分享一段我喜欢的话给大家："我所理解的自由不是想干什么就干什么，而是想不干什么就不干什么。当你还没有能力说不得时候，就努力让自己变得强大，拥有说不得权利。"
