[大数据]【MySQL】触发器

触发器

1 介绍

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

在触发器的SQL语句集合中，我们可以使用OLD和NEW来引用即将被或已经被insert/update/delete的数据，在这一点上，MySQL数据库与其他的数据库是相似的，但是MySQL的触发器现在还只支持行级触发，不支持语句级触发，行级触发指当我们执行一条SQL语句时，对多少行数据产生了影响，就会触发多少次触发器，而语句级触发指执行一条SQL语句无论对多少行数据产生了影响，都只会触发一次。

2 语法

1.创建触发器

CREATE TRIGGER trigger_name 
BEFORE/AFTER INSERT/UPDATE/DELETE -- 指定对何种类型的语句触发，以及执行前还是执行后触发
ON tbl_name FOR EACH ROW -- 指定表名，并指定当前触发器为行级触发器(只有这个选项) 
BEGIN
	trigger_stmt ; -- 编写触发器执行后所执行的语句
END;

2.查看触发器

SHOW TRIGGERS ;

3.删除触发器

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

3 案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改， 删除 ;

表结构准备:

tb_user

create table tb_user( 
    id int primary key auto_increment comment '主键', 
    name varchar(50) not null comment '用户名', 
    phone varchar(11) not null comment '手机号', 
    email varchar(100) comment '邮箱', 
    profession varchar(11) comment '专业', 
    age tinyint unsigned comment '年龄', 
    gender char(1) comment '性别 , 1: 男, 2: 女', 
    status char(1) comment '状态', 
    createtime datetime comment '创建时间' 
) comment '系统用户表';

user_logs

-- 准备工作:日志表 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.插入数据触发器

create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    -- 可以通过'new.字段名'获取即将插入的数据的字段值
    VALUES(null, 'insert', now(), new.id, 
           concat('插入的数据内容为: id=',new.id,',name=',new.name,', phone=', new.phone,
                                  ', email=', new.email, ', profession=', new.profession
                 )
          );
end;

测试

-- 查看触发器是否已经创建
show triggers ;

-- 插入数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

当我们在tb_user中插入一条数据后，触发器会被触发，同时往user_logs表中也插入一条数据：

2.修改数据触发器

create trigger tb_user_update_trigger
after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES(null, 'update', now(), new.id,
           -- 可以通过'old.字段名'获取即将被更新的数据的字段值，通过'new.字段名'获取更新后的数据的字段值
           concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, 
                                ', email=', old.email, ', profession=', old.profession,  
                  '|更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, 
                                ', email=', new.email, ', profession=', new.profession
                 )
          );
end;

测试

-- 查看
show triggers ;
-- 更新
update tb_user set profession = '会计' where id = 23;
update tb_user set profession = '会计' where id <= 5;

这里更新了多条数据，由于mysql中的触发器只支持行级触发，因此会在user_logs插入多条更新记录

3.删除数据触发器

create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES(null, 'delete', now(), old.id,
           -- 可以通过'old.字段名'获取即将被删除的数据的字段值
           concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone,
                                ', email=', old.email, ', profession=', old.profession
                  )
           );
end;

测试

-- 查看 
show triggers ; 
-- 删除数据 
delete from tb_user where id = 26;

再查看user_logs表，可以发现删除记录也被添加进去了