一、基本概念
触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行
触发器:trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。
二、作用
- 可在写入数据前,强制检验或者转换数据(保证护数据安全)
- 触发器发生错误时,前面用户已经执行成功的操作会被撤销,类似事务的回滚
三、创建触发器
基本语法
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;