第六章：触发器【mysql数据库-进阶】

1.介绍

触发器是与表有关的数据库对象,指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLd和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

触发器的类型：

2.语法

【创建】

CREATE TRIGGER trigger_ name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl name FOR EACH ROW -- 行级触发器
BEGIN 
trigger_ stmt ;
END;

【查看】

SHOW TRIGGERS ;

【删除 】

DROP TRIGGER [schema_ name.]trigger_ name; --如果没有指定schema_ name,默认为当前数据库。

3.举例

3.1 定义插入数据的触发器

通过触发器记录tb_ user 表的数据变更日志,将变更日志插入到日志表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;

定义插入数据触发器

-- 定义触发器
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)
    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 ;

drop  trigger tb_user_insert_trigger;

-- 删除
drop triggers tb_user_insert_trigger；

验证是否成功，往user表中插入二条数据：

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

select * from user_logs;

3.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,
         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;

请问执行下面的sql的语句会执行几次触发器：

update tb_user set profession='会计' where id<=5;

答案是执行5次，因为执行的行级触发器，每次往数据库表中修改一行的数据都会触发一次。

3.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,
         concat('删除之前的数据:id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,'profession=',old.profession));
end;

测试：

-- 删除一条数据
delete from tb_user where id=28;
select * from user_logs;