MySQL | 触发器
一、基本概念
触发器是一种特殊类型的存储过程,它不同于存储过程,主要是通过事件触发而被执行的,即不是主动调用而执行的;而存储过程则需要主动调用其名字执行
触发器: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
-
例子
1.首先创建两张表,商品表和订单
-
-
- 如果订单表发生数据插入,对应的商品库存应该减少。因此这里对订单表创建触发器
-
-- 1.不完整触发器(不管给多少个都只减一个)
create trigger after_insert_order after insert on orders
for each row
begin
-- 更新商品表的库存,这里只指定了更新第一件商品的库存
update goods set goods_num = goods_num - 1 where id = 1;
end;
-- 2.存在库存不够没法终止的问题
create trigger after_insert_order
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;
-- 3.完整的触发器
create trigger before_insert_order before insert on orders for each row begin
-- 取出 goods 表中对应 id 的库存
-- new 代表 orders 表中新增的数据
select goods_num from goods where id = new.goods_id into @num;
-- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
-- 如果库存不够,中断操作
if @num > new.goods_num then
-- 中断操作:暴力解决,主动出错
update goods set goods_num = goods_num - new.goods_num where id = new.goods_id;
end if;
if @num < new.goods_num then
-- 中断操作:暴力解决,主动出错
insert into xxx values(xxx);
end if;
end;
-- 3、查看触发器
-- 查看全部触发器
-- 语法:show triggers;
SHOW TRIGGERS;
-- 4.触发触发器
SELECT* from orders;
SELECT* from goods;
INSERT into orders VALUES(null,1,20);
DELETE FROM orders where id=3;
-- 六、删除触发器
-- 触发器不能修改,只能删除
-- 语法:drop trigger + 触发器名字
drop TRIGGER before_insert_order;