Mysql触发器(附案例)

触发器简介

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

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

前置准备：

主表，tb_user_pro表结构与数据如下：

日志表

-- 准备工作 : 日志表 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;

下面从三个语句类型(insert/update/delete)进行操作

1、insert类型

-- 插入数据触发器-- after: 在插入语句后执行
-- each row: 为行级触发器 
-- on后面: 监控的表的名字
create trigger tb_user_insert_triggerafter insert on tb_user_pro for each row
-- 编写触发器逻辑
begin insert into user_logs(id, operation, operation_time, operate_id, operate_params) VALUES(null, 'insert', now(), new.id, CONCAT('插入数据内容为：id=', new.id, ', name=', new.name2, ', gender=', new.gender));end;-- 查看触发器
show triggers;

查看结果：

删除触发器：

-- 删除触发器
drop trigger tb_user_insert_trigger

我们对主表执行插入操作：

insert into tb_user_pro(name2, gender) values ('小王', '男');

看触发器是否向我们的日志表插入了数据，执行结果：

结果执行成功

2、update类型

这里和上面就很类似了，直接用例子来看：

-- 更新数据触发器-- after: 在插入语句后执行
-- each row: 为行级触发器 
-- on后面: 监控的表的名字
create trigger tb_user_update_triggerafter update on tb_user_pro for each row
-- 编写触发器逻辑
begin insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES(null, 'update', now(), new.id, CONCAT('更新前数据内容为：id=', old.id, ', name=', old.name2, ', gender=', old.gender, ' | 更新后数据内容为：id=', new.id, ', name=', new.name2, ', gender=', new.gender));
end;-- 查看触发器
show triggers;-- 删除触发器
drop trigger tb_user_update_trigger

触发器创建成功：

对主表执行更新语句：

-- 执行更新操作
update tb_user_pro set gender='女' where id = 5;

tb_user_pro表更新后的数据：

日志表数据：

3、delete类型

-- 删除数据触发器-- after: 在插入语句后执行
-- each row: 为行级触发器 
-- on后面: 监控的表的名字
create trigger tb_user_delete_triggerafter delete on tb_user_pro for each row
-- 编写触发器逻辑
begin insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES(null, 'delete', now(), old.id, CONCAT('删除前数据内容为：id=', old.id, ', name=', old.name2, ', gender=', old.gender));
end;-- 查看触发器
show triggers;-- 删除触发器
drop trigger tb_user_delete_trigger

查看触发器：

对tb_user_pro执行删除操作：

-- 执行删除操作
delete from tb_user_pro where id = 5;

日志表：

总结

总的来说，触发器就是通过监控一个主表对应类型的操作，随后定义在其执行对应类型操作的时候要执行的一些逻辑，本文案例的逻辑就是插入日志表一些数据。