MySQL as if触发器应用_mysql触发器基础运用解读

触发器定义：

触发器是与表操作有关的数据库对象，当触发器所在表上发生指定事件时，将调用该对象。(触发器的这种特性可以协助应用在数据库端确保数据的完整性)

本质意义：

脱离程序代码的数据库表数据更新的(更新，插入，删除)逻辑实现。只要触发器所在表数据改变满足触发条件，执行对应的sql语句去执行更新操作。(用于维护数据完整性，常见订单信息的更新等)

优缺点

参考：MySQL触发器的利弊(https://itbilu.com/database/mysql/4y77zMXel.html)

优点(常见应用场景)

基于行数据变更的日志记录。如：在用户订单系统中，我们可以基于用户订单数据状态的改变，使用触发器构建用户订单日志表数据。

基于行数据变更的关系数据的更新。如：用户订单改变至付款或相关状态时，我们可以基于用户订单数据状态的改变，使用触发器改变用户会付款或相应状态信息。

基于行数据变更的数据汇总。如：用户订单成交或失败，我们可以基于用户订单数据状态的改变，使用触发器构建用户总成交量或失败量汇总数据。

缺点

MySQL触发器能基于行触发，MySQL触发器始终时基于表中的一条记录触发，而不是一组SQL语句。因此，如果需要变动整个数据集而数据集数据量又较大时，触发器效果会非常低。

一个MySQL触发器可能会关联到另外一张表或几张表的操作。因此，会导致数据库服务器负荷也会相应的增加一倍或几倍，如果出现因为触发器问题导致的性能问题，会很难定位问题位置和原因。

在基于锁的操作中，触发器可能会导致锁等待或死锁。触发器执行失败，原来执行的SQL语名也会执行失败。而因为触发器导致的失败结果和失败原因，往往很难排查。

代码结构：

触发器申明头(触发器名称，触发时间节点位置(更改前后)，触发事件[增删改]，表名)

触发器实体-body(begin [sql] end ) 可以申明变量

代码语法：

-- 1. 新建触发器

CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

触发器程序体

END

语法解析：

-- {BEFORE|AFTER} 触发器触发的时机

-- {INSERT|UPDATE|DELETE} 触发器事件

-- FOR EACH ROW 子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

-- (针对insert和delete语句，每一行都触发)

--2. 查看触发器

show triggers\G

-- 3. 删除

drop trigger 触发器名称

实例：

(1.)AFTER触发器

在section表上定义一个INSERT触发器，当对课程section表执行任何插入操作后触发器被启动，以确保插入元组的time_slot_id属性即上课时间段是合法的，受time_slot表的time_slot_id约束，检查插入时的参照完整性。

delimiter

create trigger timeslot_check1 after insert

on section for each row

begin

if(NEW.time_slot_id not in

(select time_slot_id

from time_slot))

then

delete from section

where time_slot_id = NEW.time_slot_id;

end if;

end

(2.) BEFORE触发器

在takes表上定义一个before insert触发器，插入一条记录时，假设所插入的分数的

值为空白则表明该分数发生缺失，所以定义此触发器在分数值条件满足时触发用null值代替空白。

delimiter

create trigger setnull before insert

on takes for each row

begin

if(NEW.grade = ' ')

then

set NEW.grade = null;

end if;

end

总结：

after触发器—是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作

before触发器—是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作，如：我们在触发之前需要判断new值和old值的大小或关系，如果满足要求就触发，不通过就修改再触发；如：表之间定义的有外键，在删除主键时，必须要先删除外键表，这时就有先后之分，这里before相当于设置了断点，我们可以处理删除外键。

针对insert操作，new表示的是插入的值, 只有NEW是合法的；

针对delete操作，old表示的是删除后的值，只有OLD才合法；

针对update操作，new表示的是更新后的值，old表示的是原来的数据，NEW、OLD可以同时使用。