SQL学习笔记-------第八天（触发器实现多表同步更新学生信息）

---

1.什么是触发器？

触发器是由一段代码块组成，当表中的数据发生变化(如INSERT、UPDATE 或 DELETE )时，会自动唤醒调用触发器中的代码，从而起到了保证业务规则和数据的完整性的作用。

比如在网上商城系统中，当商家的一个商品被用户购买时，在订单表生成数据的同时，需要从商家库存表中减去该商品。

还有当一个商家账户注销时，删除用户表中该商家记录的同时，还需要将商家对应的商品数据，销售订单记录和库存记录等信息删除，这些都可以使用触发器来完成。

触发器也可以起到字段约束的作用，用来检查字段的新增值是否符合规则，如果异常及时返回错误，阻塞插入。

2.创建触发器

不同数据库创建触发器的语法存在细微的差距，在MariaDB(MySQL分支)中创建触发器语法如下：

create trigger trigger_name trigger_time trigger_event on table_name for each now trigger_stmt

其中：

• trigger_name:触发器名称，开发者自定义；
• trigger_time:什么时候触发，取值为BEFORE或AFTER，表示是在trigger_event事件之前还是之后执行触发器。
• trigger_event:触发事件，取值为insert、update、delete;
• table_name:触发器相关联的表名，即在哪张表上进行操作室执行触发器。
• trigger_stmt:触发器代码块，一条或多条SQL语句；
• fo reach now:每影响一行记录，就触发执行一次触发器的SQL；

从 trigger_time 和 trigger_event 的组合来看可以创建 6 种触发器，即：BEFORE INSERT、BEFORE
UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE；

另外在 MariaDB 中限制在同一张表不能创建两种相同类型的触发器，因此在一张表最多创建6个触发器。

以下 SQL 脚本是在 MariaDB 中的学生选课成绩表 elective 上创建 after insert 的触发器，它的主要作用是在当向学生选课信息 student 表新增一条记录后，更新学生信息表中对应学生的总学分成绩。

delimiter //
drop trigger if exists tri_elective_insert //
create trigger tri_elective_insert after insert
on elective for each row
begin
declare number int;
select (case when grade >= 60 then 5 else 4 end ) into number from elective where id= new.id;
update student set sum_grade = number +  ifnull(sum_grade,0) where sid = new.sid;
end //
delimiter ;

可以看到上面脚本中定义了 new 和 old 这两个关键字代表 elective 表触发了触发器的那行数据，new表示修改后 (update语句)或插入 (insert语句）的新记录，相反 old 表示修改前（update语句）或被删除（delete语句）的原数据。

SQL脚本中的关键字 DELIMITER 起到申明 SQL 语句结束符的作用，当然，MySQL 结束符是可以自由设定的，可以用 “/” 或者“$$”等字符 ,如果不设置结束符，MySQL 的默认 SQL 语句结束符为";"。

由于上面 SQL 脚本是一个整体，需要通过 DELIMITER 申明结束符"//"，让数据库明白，当再次遇到这个符号时脚本才算完整，可以执行前面的脚本了，脚本最后需要将结束符重新改为分号：DELIMITER ;

我们通过以下SQL脚本测试下刚刚创建的触发器 tri_elective_insert：

insert into elective(sid,cid,createtime,grade) values('1006','31',now(),93);
 select  * from student where sid='1006';

当执行上面的脚本向学生成绩信息表 elective 新增一条记录时，会触发触发器中脚本的执行，通过查询 SQL 可以看到编号为 1006 的总学分成绩从 0 已经变成了 5。

3.查看和删除触发器

在 MySQL 中查看触发器，使用下面的SQL语句：

show triggers

结果集：

查询语句会输出当前库所有的触发器，如果希望查询一张表的触发器，可以通过查询系统表information_schema.triggers 来实现，比如查询 elective表中的所有的触发器：

select * from information_schema.triggers  where event_object_table='elective'

结果集：

MySQL 数据库提供了删除触发器的语法：

DROP TRIGGER [if exists] trigger_name --删除触发器 

值得注意的是，如果触发器不需要了一定及时删除，以免造成意外操作导致数据的混乱。

4.小结

本节我们一起学习了数据库表的触发器，但这里给大家的建议是在项目开发过程中，尽量不要使用触发器，这是为什么呢？ 主要有以下两点考虑：

1. 假如我们想向表 elective 中插入上万条记录，那么触发器就会被触发上万次，触发器的每次执行都会消耗资源，
2. 触发的次数越多，相应消耗的资源越多；
3. 触发器对开发者来说是自动执行的，当出现数据不一致或数据混乱时，不容易定位问题，触发器的代码也是最容易被忽略的；
4. 如果已经使用了触发器，那么可以考虑将触发器中的代码迁移到存储过程中或者写入应用程序中处理，这样可以规避触发器带来的负面影响。

---------------------------------------------------------总结自慕课网（10年DBA玩转SQL）一文----------------------------------------------