MySQL触发器

思考：使用存储过程（含事务）可以保证数据库的业务完整性和数据的一致性，但是如果用户不是调用存储过程，而是直接修改数据库中的数据呢？比如：数据库管理员在数据库中执行以下操作，可能会破坏数据库的一致性

• 在余额表中直接给某账号加钱

• 删除或修改流水表中的数据

• 修改余额表中的账号

解决方法：触发器（trigger）

一、触发器的作用和工作机制

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

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

分类：after触发器和before触发器

常见的触发器由update/insert/delete事件触发，这种触发器称为DML触发器

与存储过程不同：触发器是通过事件触发，存储过程需要调用才执行

二、触发器定义的语法

触发器定义的语法

create trigger 触发器名 触发时间 触发事件 on 表名 for each row

begin

触发程序

end;

• 触发事件：befor | after

• 触发事件：insert | delete | update

• 行级触发器：for each row

创建before类型触发器

向students表中添加一条成绩信息之前，进行成绩的求和运算

create trigger sumofgrade before insert on students for each row
begin
 set @sum=@sum+NEW.grade;
end;
#测试
select @sum:=(select sum(grade) from students);
insert into student(sno,cno,grade) values('20220101','002',85);
select @sum 总成绩;

创建after类型触发器

创建触发器delete_trigger，记录删除“选课表”中的数据的用户及删除的时间

1. 创建“日志信息表”rec_log，用于存储用户对表的操作

create table rec_log(
    user varchar(30),
    op_time timestamp
);

2. 在“学生表”上创建触发器，它会在用户对“学生表”使用delete操作时触发，并向rec_log表中添加操作的用户名和日期

create trigger delete_trigger after delete onsc for each row
insert into rec_log(user,date)values(user(),current_time);

3. 测试

delete from sc where sno='20220202' and cno='002';

激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行

一个数据表上可能定义了多个触发器，同一个表上的多个触发器激活时遵循如下的执行顺序

1. 执行该表上的before触发器

2. 激活触发器的SQL语句

3. 执行该表上的after触发器

当一个数据表定义了多个相同类型的触发器时，原则上按照触发器的定义顺序执行

old表和new表

DML触发器使用两个临时表：old表和new表

• 执行insert操作时，被添加的行存储在new表中

• 执行delete操作时，被删除的行会存储在old表中

• 执行update操作时，被修改的行存储在old表中，修改后的数据存储在new表中

在mysql触发器的触发体中，通过new表和old表获取数据表中变化前后的值，“old.列名”表示变化前的值，“new.列名“表示变化后的值

这样SQL语句就可以访问受触发语句影响的每行的列值，例如可以通过触发器实现级联删除或更新

级联删除示例

• 查看sc表中学号为20220505的选课信息

• 删除s表中学号为20220505的学生信息（可以删除吗）不可以

创建级联删除的触发器（当根据学号删除同个学生时，同时级联删除该学生所对应的选课信息）

create trigger delete_trigger before delete on s for each row
delete from sc where sno=old.sno;

再次删除学生信息就成功了

查看和删除触发器

查看触发器

查看所有触发器：

show triggers;

查看某一个触发器的定义

show create trigger delete_trigger;

删除触发器

drop trigger 触发器名

drop trigger delete_trigger;

MySQL使用触发器的注意事项

• 同一张表不能创建两个相同触发时间、触发事件的触发程序

• 触发程序中不能使用以显示或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或set autocommit=0等

• 触发器针对记录进行操作，当批量更新数据时，引入触发器会导致批量更新操作的性能降低

• 触发程序不能对本表执行update操作，否则可能出现错误信息，甚至可能陷入死循环，update操作可用set命令代替

• 使用触发器维护InnoDB外键约束的级联选项时，应该首先维护子表的数据，然后再维护父表的数据，否则可能出现错误

触发器例子

1. 为s表设计一个触发器 tri_s_after_insert，当向S表中录入学生信息时，自动将该学生的学号和课程编号为002（注意：如果没有这门课程编号，可任意换一门课程编号）录入到sc表中，用户自定义变量 message 的值设为：SNO+的选课信息已录入成绩表。在触发器外部查询message；

create trigger tri_s_after_insert after insert on s for each row
begin
    insert into sc(sno,cno)values(new.sno,'002');
    select sno into @in_sno from sc where sno=new.sno;
    set @message=@in_sno+'的选课信息已录入成绩表';
end;
insert into s values('2171317','张三','2000-06-21','计算机系');
select @message;

2. 为sc表设计一个BEFORE类型触发器tri_sc_before_update，当修改选课表的成绩时，如果修改后的成绩<60,则将该成绩对应的学分修改为0，否则，则为该门课程对应的学分。（要求触发语句: 修改你的某一门成绩，如果数据库中你没有选课，则灵活的先给你自己选一门课）提示：先修改选课表结构，增加获得学分列credit

alter table sc add column credit int;
create trigger tri_sc_before_update before update on sc for each row
begin
    select credit into @new_credit from c where cno=new.cno;
    if new.grade>=60 then
        set new.credit=@new_credit;
    else
        set new.credit=0;
    end if;
end;
update sc set grade=80 where sno='20710317' and cno='002';
select * from sc where sno='20710317';