触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
| 触发器类型 | NEW 和 OLD |
|---|---|
| INSERT型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
| DELETE型触发器 | OLD 表示将要或者已经删除的数据 |
语法
创建
| 1 2 3 4 5 6 | CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW — 行级触发器 BEGIN trigger_stmt; END; |
查看
| 1 | SHOW TRIGGERS ; |
删除
| 1 | DROP TRIGGER [schema_name.]trigger_name ; — 如果没有指定 schema_name，默认为当前数据库 。 |
案例
通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加,修改 , 删除 ;
表结构准备:
| 1 2 3 4 5 6 7 8 9 | — 准备工作 : 日志表 user_logs create table user_logs( id int(11) not null auto_increment, operation varchar(20) not null comment ‘操作类型, insert/update/delete’, operate_time datetime not null comment ‘操作时间’, operate_id int(11) not null comment ‘操作的ID’, operate_params varchar(500) comment ‘操作参数’, primary key(`id`) )engine=innodb default charset=utf8; |
插入数据触发器
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | drop trigger if exists user_insert_trigger; create trigger user_insert_trigger after insert on tb_user for each row begin insert into user_logs(operation, operate_time, operate_id, operate_params) values(‘insert’, now(), new.id, concat(‘插入的内容为：id=’, new.id, ‘, name=’, new.name, ‘, phone=’, new.phone, ‘, email=’, new.email, ‘, profession=’, new.profession, ‘, age=’, new.age, ‘, gender=’, new.gender, ‘, status=’, new.status)); end mysql> select * from user_logs; +—-+———–+———————+————+—————————————————————————————————————————–+ | id | operation | operate_time | operate_id | operate_params | +—-+———–+———————+————+—————————————————————————————————————————–+ | 1 | insert | 2022-08-08 15:57:16 | 25 | 插入的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4 | +—-+———–+———————+————+—————————————————————————————————————————–+ 1 row in set (0.00 sec) |
修改数据触发器
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | drop trigger if exists user_update_trigger; create trigger user_update_trigger after update on tb_user for each row begin insert into user_logs(operation, operate_time, operate_id, operate_params) values(‘update’, now(), old.id, concat(‘更新前的内容为：id=’, old.id, ‘, name=’, old.name, ‘, phone=’, old.phone, ‘, email=’, old.email, ‘, profession=’, old.profession, ‘, age=’, old.age, ‘, gender=’, old.gender, ‘, status=’, old.status, ‘；更新后的内容为：id=’, new.id, ‘, name=’, new.name, ‘, phone=’, new.phone, ‘, email=’, new.email, ‘, profession=’, new.profession, ‘, age=’, new.age, ‘, gender=’, new.gender, ‘, status=’, new.status)); end; mysql> UPDATE `studydata`.`tb_user` SET `name` = ‘孙尚香’, `phone` = ‘17799993333’, `email` = ‘33333844@qq.com’, `profession` = ‘舞蹈’, `age` = 29, `gender` = ‘1’, `status` = ‘4’, `createtime` = ‘2103-05-26 00:00:00’ WHERE `id` = 25; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user_logs order by id desc limit 1 \G; *************************** 1. row *************************** id: 2 operation: update operate_time: 2022-08-08 16:04:42 operate_id: 25 operate_params: 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4 1 row in set (0.00 sec) mysql> update tb_user set profession=’客服’ where id>20; Query OK, 5 rows affected (0.06 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from user_logs; +—-+———–+———————+————+————————————————————————————————————————————————————————————————————————————————————–+ | id | operation | operate_time | operate_id | operate_params | +—-+———–+———————+————+————————————————————————————————————————————————————————————————————————————————————–+ | 1 | insert | 2022-08-08 15:57:16 | 25 | 插入的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4 | | 2 | update | 2022-08-08 16:04:42 | 25 | 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4 | | 3 | update | 2022-08-08 16:08:25 | 21 | 更新前的内容为：id=21, name=典韦, phone=17799990020, email=ycaunanjian@163.com, profession=城市规划, age=52, gender=1, status=2；更新后的内容为：id=21, name=典韦, phone=17799990020, email=ycaunanjian@163.com, profession=客服, age=52, gender=1, status=2 | | 4 | update | 2022-08-08 16:08:25 | 22 | 更新前的内容为：id=22, name=廉颇, phone=17799990021, email=lianpo321@126.com, profession=土木工程, age=19, gender=1, status=3；更新后的内容为：id=22, name=廉颇, phone=17799990021, email=lianpo321@126.com, profession=客服, age=19, gender=1, status=3 | | 5 | update | 2022-08-08 16:08:25 | 23 | 更新前的内容为：id=23, name=后羿, phone=17799990022, email=altycj2000@139.com, profession=城市园林, age=20, gender=1, status=0；更新后的内容为：id=23, name=后羿, phone=17799990022, email=altycj2000@139.com, profession=客服, age=20, gender=1, status=0 | | 6 | update | 2022-08-08 16:08:25 | 24 | 更新前的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=客服, age=29, gender=1, status=4 | | 7 | update | 2022-08-08 16:08:25 | 25 | 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=客服, age=29, gender=1, status=4 | +—-+———–+———————+————+————————————————————————————————————————————————————————————————————————————————————–+ 7 rows in set (0.00 sec) |
删除数据触发器
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | drop trigger if exists user_delete_trigger; create trigger user_delete_trigger after delete on tb_user for each row begin insert into user_logs(operation, operate_time, operate_id, operate_params) values(‘delete’, now(), old.id, concat(‘删除的内容为：id=’, old.id, ‘, name=’, old.name, ‘, phone=’, old.phone, ‘, email=’, old.email, ‘, profession=’, old.profession, ‘, age=’, old.age, ‘, gender=’, old.gender, ‘, status=’, old.status)); end; mysql> delete from tb_user where id = 24; Query OK, 1 row affected (0.00 sec) mysql> select * from user_logs order by id desc limit 1; +—-+———–+———————+————+————————————————————————————————————————-+ | id | operation | operate_time | operate_id | operate_params | +—-+———–+———————+————+————————————————————————————————————————-+ | 8 | delete | 2022-08-08 16:18:43 | 24 | 删除的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=客服, age=29, gender=1, status=4 | +—-+———–+———————+————+————————————————————————————————————————-+ 1 row in set (0.00 sec) |
