数据库触发器

一、触发器的概念

触发器（ trigger）是 mysql 提供的一个特殊的存储过程函数，保证了数据完整性的一种方法，它由事件来出发，当对一个表进行操作（insert、delete、update）时它就会激活预设的sql代码。

• 触发器有行级触发器和语句级触发器之分，mysql中只支持行级触发器
• 同一类型的触发器在表中可以有多个
• 属于事务机制

二、什么时候使用触发器？

1. 需要基于数据库的值使用户具有操作数据库的某种权利
2. 需要基于时间限制用户的操作
3. 需要基于数据库中的数据限制用户的操作
4. 需要跟踪用户对数据库的操作
5. 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。
6. 触发器能够对数据库中相关的表进行连环更新。在改动或删除时级联改动或删除其他表中的与之匹配的行。
7. 触发器可以拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。
8. 同步实时地复制表中的数据。
9. 自己主动计算数据值，假设数据的值达到了一定的要求，则进行特定的处理。

三、创建触发器

（1）触发时间（after/before）
（2）监视事件（insert/update/delete）
（3）监视地点（table）
（4）触发事件 (begin…end)

create trigger [触发器名称]
after/before insert/update/delete on [表名]
for each row
begin
......
end

• after操作，是在执行了监视动作后，才会执行触发事件
• before操作，是在执行了监视动作前，会执行触发事件
• for each row 行级监视（数据库每更新了一条数据，相应的执行一次触发事件）

四、触发器的查看与删除

//查看已有的触发器
show trigger
//删除触发器
drop trigger [触发器名称]

五、触发器的使用

1、数据同步

重置了student表里的Cno列和Sscore列（Sscore_total列是学生所选课程的总成绩）

添加了student_course 学生选课表

以及course 课程表

学生通过更新（或插入）选课表，将会同步更新（或插入）学生在student中的Cno值和score_total值

创建存储函数进行更新（插入）选课表数据：

create procedure insert_course(S int,C int)
leave_cancel://定义跳出存储过程标记
begin
if(select count(*) from student_course where Sno=S and Cno=C)
then
select 'data is exist!' as state;//判断用户是否已经存在
leave leave_cancel;//已存在时跳出函数，不做插入
else
insert into student_course(Sno,Cno) values(S,C);
end if;
end

创建登分的存储过程update_score

delimiter $$
create procedure update_score(S int,C int,score int)
begin
if(select count(*) from student_course where Sno=S and Cno=C)
then
update student_course set Sscore=score where Sno=S and Cno=C;
else
select 'student is not exist!' as state;
end if;
end$$

假设我们要在student_courese表上更新成绩，并且在student表的总成绩同步更新
那么就需要做两件事：

1、调用存储过程insert_score
call insert_score(2,1004)
2、更新student中的总成绩列
update student set Sscore_total=Sscore_total+new.Sscore where Sno=new.Sno;

创建触发器update_score 监测student_course表每更新一行，就把score值和原来的值相加。

delimiter $$
create trigger tri_update_score after update on student_course
for each row
begin
update student set Sscore_total=Sscore_total-old.Sscore+new.Sscore where Sno=new.Sno;
end$$
delimiter ;

调用两次更新成绩存储过程，学号为2的同学选修了两门课，总成绩是188分：

• Sscore_total=Sscore_total+new.Sscore 这里更新的列不能为空，初始设为0才可以
• 我们可以只用插入选课表中一条数据，就会自动触发数据插入学生表的操作，这样就可以通过一个表的操作，同步其他操作，实现自动化维护数据的目的。
• old和new：insert操作会带来新的行，new.xxx代表新插入的行，delete操作会丢掉旧的行，old.xxx代表被删除的行，而update操作会带来新的行也会失去旧的行，所以update可以同时使用old和new。
• 如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。