- MySQL - 触发器 trigger

触发器是什么

触发器是一种与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发；比如对一个表进行insert、update、delete操作时，就会触发它执行；SQL3的触发器是一个能由系统自动执行的对数据库进行修改的语句；

触发器与存储过程唯一的区别是：触发器不能执行execute语句调用，而是在执行sql语句时自动触发执行；

触发器的特点、作用

• 触发器可以查询其他表，可以包含复杂的SQL语句；
• 触发器经常用于强制服从复杂的业务规则或要求、强制引用完整性；
• 触发器可在写入数据前，强制检验或转换数据；
• 触发器执行是若是发生错误，错误的结果会被撤销；
• 触发器可以根据相关表进行级联修改，不过通过级联引用完整性约束可以更有效的执行这些更改；
• MySQL不可以在视图上创建触发器；sql server没说不可以；

触发器和约束

• 触发器可以支持约束的所有功能；
• 触发器可以引用其他表的列来指定约束，而sql的约束只能通过逻辑表达式或同表中的列来进行；比如可以根据用户表的登录状态限制订单表的插入订单操作；所以如果程序需要使用另一个表中的列来进行验证，则必须使用触发器；
• 约束只能输出标准的系统错误信息，触发器可以输出自定义错误信息，也可以进行较为复杂的错误处理操作；

触发器的分类：(sql server)

1、DML触发器：
当数据库表中的数据发生改变时，包括insert、update、delete等任意操作，如果对该表写了对应的DML触发器，那么该触发器就会自动执行；

DML触发器主要作用是：强制执行业务规则，扩展sql server约束、默认值等；因为约束只能约束同一个表中的数据，而触发器可以执行任意的SQL命令；

2、DDL触发器：
主要用于审核与规范对数据库中的表、触发器、视图等结构上的操作；比如修改/新增表、列等；它是在数据库结构发生变化时触发执行，只要用它来记录数据库的修改过程，以及限制对数据库的修改，比如不允许删除某些指定表；

3、登录触发器： 发生login事件时触发执行；
与数据库建立会话连接时，登录触发器将在登录的身份验证完成之后，用户会话实际建立之前触发执行，如果身份验证失败，就不会触发登录触发器；

触发器的查询语句

• 查看触发器：show trigger from dbName;
• 删除触发器：drop trigger dbName.triggerName;

触发器语法

delimiter $$
create trigger dbName.triggerName on tableName
before / after            // 触发器时机
insert / update / delete  // 触发器事件
for each row
biginsql 语句
end $$
delimiter ;

触发器事件

由触发器的两种时机和触发器的三种事件，可以建立6种触发器；

MySQL中除了insert、update、delete三种基本触发器事件外，还定义了load data、replace事件，这两种语句也能引起上面6中触发器的触发；

load data：用于将一个文件装入到数据表中，相当于批量执行insert操作；
replace：在表中有主键、唯一约束时，插入相同记录时，会先删除原来的记录，再执行insert操作；

new、old关键字

与sql server中的inserted、deleted类似，用来表示触发器所在的表中，触发了触发器的那行数据；

old是只读的，new可以使用set进行赋值；

• insert触发器中：new表示将要(before)、或者已经(after)插入的新记录；
• update触发器中：old表示将要或已经被修改的原数据，new表示将要或已经被修改的新数据；
• delete触发器中：old表示将要或已经被删除的原数据；

触发器执行顺序

• 若brfore触发器执行失败，SQL语句无法正确执行；
• 若SQL执行失败，after触发器不会触发；
• after触发器执行失败，SQL会回滚；

事例

假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

delemiter $$
create trigger tri_stuInsert on student
after
insert
for each row
begindeclare count int;set count = (select stuCount from class where classId = new.classId);update class set stuCount = count + 1 where classId = new.classId;
end $$
delimiter ;