mysql触发器的学习

一、基本概念

触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。
 

二、作用

1. 可在写入数据前，强制检验或者转换数据(保证护数据安全)
2. 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
    

三、创建触发器

基本语法

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件
on 表
for each rowbegin-- 触发器内容主体，每行用分号结尾
end自定义的结束符合
delimiter ;

on 表
for each：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会发生
 

触发时间

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

• before：表中数据发生改变前的状态
• after：表中数据发生改变后的状态

PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)
 

触发事件

触发器是针对数据发送改变才会被触发，对应的操作只有

• INSERT
• DELETE
• UPDATE
   

注意事项

• 在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
• 每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update 

四、例子

#建立商品表goods和订单表orders
create table goods(id int auto_increment primary key,name varchar(50),goods_num int); 
insert into goods values(1,'手机',100),(2,'电脑',100),(3,'游戏机',50);
select*from goods;
create table orders(id int auto_increment primary key,goods_id int,goods_num int);
insert into orders values(1,1,5),(2,2,10);
select*from orders;

#创建触发器s1

create trigger s1 
after insert on orders for each row
begin
-- 更新商品表的库存，这里只指定了更新第一件商品的库存
update goods set goods_num=goods_num-1 where id=1;
end;
show triggers; #查看全部触发器
show create trigger s1; #查看触发器的创建语句
insert into orders values(null,1,5); #给orders插入数据被动触发 触发器 orders表增加,goods表减少
-- 但是在orders表中goods_num无论增加多少，在goods表中的goods_num只会-1
select*from goods;
select*from orders;

#删除触发器s1
drop trigger s1;

#创建触发器s2

-->old和new关键字的用法

create trigger s2
after insert on orders for each row
begin
-- new 代表 orders 表中新增的数据
update goods set goods_num=goods_num-new.goods_num where id=new.goods_id;
end;
-- 插入数据后，orders表和goods表的goods_num相互对应
insert into orders values(null,2,3);
insert into orders values(null,1,5);
#删除触发器s2-->每次建立新的触发器前要删除上一个触发器
drop trigger s2;

#创建触发器s3

->如果此时商品的库存不够了，该怎么处理？

create trigger s3
before insert on orders for each row
begin
select goods_num from goods where id=new.goods_id into @num;
-- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
if @num < new.goods_num 
then
-- 如果库存不够，中断操作
-- 中断操作：暴力解决，主动出错
insert into xxx values(xxx);
end if;
end;
-- 插入超出范围的数据，orders 表和 goods 表的数据并没有得到更新
insert into orders values(null,3,100);
#删除触发器s3
drop trigger s3;

#创建触发器s4

-->mysql触发器不能对同一张表进行修改操作

create trigger s4
-- 假如我在 before update 的时候作一条更新语句，随便将里面哪个字段进行更新
before update on orders for each row
begin
update orders set goods_id = 10 where id=new.id;
end;
select*from orders;
-- 接下来我用 update 语句对 orders 表进行更新,此时报错了，提示不能进行更新。
update orders set goods_num=1  where id=8;
#因此说明：MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错
#删除触发器s4
drop trigger s4;