MySQL之触发器

1.介绍
在操作数据之前或之后触发某个语句
作用：在数据库确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容。只支持行级触发。

2.创建触发器
语法结构：
create trigger trigger_name
before/after insert/update/delete
on table_name
[for each row] --行级触发器
begin
strigger_stmt;
end;

通过触发器记录数据变更记录，包含增加，修改，删除
首先创建一张日志表
create table logs(
id int not null auto_increment,
operation varchar(20) not null comment ‘操作类型,insert/update/delete’,
operation_time datetime not null comment ‘操作时间’,
operation_id varchar(10) not null comment ‘操作表的ID’,
operation_params varchar(50) comment ‘操作参数’,
primary key(id)
)engine=innodb default charset=utf8;

创建触发器
create trigger trigger_insert
after insert
on manegers
for each row
begin
insert into logs(id,operation,operation_time,operation_id,operation_params) values(null,‘insert’,now(),new.Mid,concat(‘插入后（id:’,new.Mid,’,name:’,new.MName,‘sex:’,new.Msex,’）’));
end$

create trigger trigger_update
after update
on manegers
for each row
begin
insert into logs(id,operation,operation_time,operation_id,operation_params) values(null,‘update’,now(),new.Mid,concat(‘修改前（id:’,old.Mid,’,name:’,old.MName,‘sex:’,old.Msex,’）’));
insert into logs(id,operation,operation_time,operation_id,operation_params) values(null,‘update’,now(),new.Mid,concat(‘修改后（id:’,new.Mid,’,name:’,new.MName,‘sex:’,new.Msex,’）’));
end$

create trigger trigger_delete
after delete
on manegers
for each row
begin
insert into logs(id,operation,operation_time,operation_id,operation_params) values(null,‘delete’,now(),old.Mid,concat(‘删除后（id:’,old.Mid,’,name:’,old.MName,‘sex:’,old.Msex,’）’));
end$

3.删除触发器

drop trigger trigger_name;

4.查看触发器
show triggers\G;