【四十二、MySql】进阶篇--触发器（介绍、语法、insert/update/delete类型）--总结（视图、存储过程、存储函数、触发器）

本文链接：https://blog.csdn.net/qq_49121643/article/details/129149072

1，介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。

2，特性
可以协助应用在数据库端保持数据的完整性、日志记录、数据校验等操作。

3，使用事项
可使用别名old 和 new 来引用触发器中发生变化的记录内容，这与其它的数据库是相似的。在MySQL中只支持行级触发器，不支持语句级触发器。
在这里插入图片描述

4，语法
1）创建触发器

create trigger trigger_name
befor/after insert/update/delete  --after/before ：指代当前触发器触发的时机是之前(before)还是之后(after) 
--insert/update/delete : 指代的时插入/修改/删除表table_name时，才会触发该触发器中trigger_stmt中的逻辑
on table_name for each row --行级触发器
begin 
	trigger_stmt; --触发器里的具体逻辑实现
end;

2）查看触发器
show triggers ;
在这里插入图片描述

3）删除触发器
drop trigger [schema_name] trigger_name; 如果没有指定 schema_name，则默认为当前数据库

4，练习
通过触发器记录employee表中的数据变更日志，将变更日志插入到emp_log表中，包含增加、修改、删除

准备工作：日志表emp_log

# 准备工作：日志表emp_log
create table if not exists `emp_log`(
        `id` int(11) not null auto_increment,
        `operation` varchar(20) not null comment '操作类型，insert/update/delete',
        `operation_date` datetime comment '操作时间',
        `operation_id` int(11) not null comment '操作的id',
        `operation_params` varchar(500) not null comment '操作参数',
        primary key(`id`)
)engine=INNODB DEFAULT CHARSET=utf8mb4;

案例1：（insert类型）
创建插入数据时的触发器

# 插入数据时的触发器
create trigger emp_insert_trigger
    after insert on employee for each row # before/after ：指代当前触发器触发的时机是之前(before)还是之后(after)
                                          # insert ： 指代是插入数据时的触发器
                                          # on 后面加表名 ：指代的是操作的哪张表后触发该触发器
                                          # for each row ： 指代行级触发器
begin
    # sql逻辑 （本例子是往日志表中插入数据）
    # new : 用old还是new？本次操作是插入数据时的触发器，对于插入数据时来说，要拿到新插入的数据，得用new来获取
    # concat() : 字符串拼接
    insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
    (null,'insert',NOW(),NEW.id,concat('插入数据的内容为：【id = ',NEW.id,'】【name = ',NEW.name,'】【username = ',NEW.username,'】【password = ',NEW.password,'】……'));
end;

完成以上操作之后，查看emp_log表有无记录，OK
在这里插入图片描述

案例2：（update类型）
创建修改数据时的触发器

# 修改数据时的触发器
create trigger emp_update_trigger
    after update on employee for each row # before/after ：指代当前触发器触发的时机是之前(before)还是之后(after )
                                          # update ： 指代是修改数据时的触发器
                                          # on 后面加表名 ：指代的是操作的哪张表后触发该触发器
                                          # for each row ： 指代行级触发器
begin
    # sql逻辑 （本例子是往日志表中插入数据）
    # old & new : 在修改的操作中，old可以获取修改前的数据，new则获取将要修改或修改之后的数据
    # concat() : 字符串拼接
    insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
    (null,'update',NOW(),NEW.id,concat('修改前数据的内容为：【id = ',OLD.id,'】【name = ',OLD.name,'】【username = ',OLD.username,'】【password = ',OLD.password,'】……',
      '修改后数据的内容为：【id = ',NEW.id,'】【name = ',NEW.name,'】【username = ',NEW.username,'】【password = ',NEW.password,'】……'  ));
end;

# 修改表employee中的数据，并查询表emp_log测试触发器emp_update_trigger有没有被触发，
update employee set name = '你好你好' where id = 413;

完成以上操作之后，查看emp_log表有无记录，OK
在这里插入图片描述

继续，执行如下sql，查看触发器执行多少？
update employee set name = '你好你好' where id >= 5 and id <= 10;

答案是6次，因为加了for each row行级触发器，update影响了多少行，就会触发多少次
在这里插入图片描述

案例3：（delete类型）
创建删除数据时的触发器

# 删除数据时的触发器
create trigger emp_delete_trigger
    after delete on employee for each row # before/after ：指代当前触发器触发的时机是之前(before)还是之后(after )
                                          # delete ： 指代是删除数据时的触发器
                                          # on 后面加表名 ：指代的是操作的哪张表后触发该触发器
                                          # for each row ： 指代行级触发器
begin
    # sql逻辑 （本例子是往日志表中插入数据）
    # old : 在删除的操作中，则需要用old获取删除前的数据
    # concat() : 字符串拼接
    insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
    (null,'delete',NOW(),OLD.id,concat('被删除的数据内容为：【id = ',OLD.id,'】【name = ',OLD.name,'】【username = ',OLD.username,'】【password = ',OLD.password,'】……'));
end;

# 删除表employee中id为13的数据，并查询表emp_log测试触发器emp_delete_trigger有没有被触发，
delete from employee where id = 13;