四、触发触发器
让触发器执行，让触发器指定的表中，对应的时机发生对应的操作
insert into my_order (item_id, count) values(1, 1); mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | +----+---------+-------+ 1 row in set (0.00 sec) mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手机 | 99 | | 2 | 电脑 | 100 | | 3 | 包包 | 100 | +----+--------+-------+ 3 rows in set (0.00 sec)
五、删除触发器
drop trigger 触发器名字; -- eg drop trigger after_insert_order_trigger;
六、触发器的应用
记录关键字 new old
6.1 完善
商品自动扣除库存
触发器针对的是数据表中的每条记录，每行数据再操作前后都有一个对应的状态
触发器在执行之前就将对应的数据状态获取到了：
- 将没有操作之前的数据状态都保存到
old关键字中 - 操作后的状态都放在
new中
触发器中，可以通过old和new来获取绑定表中对应的记录数据
基本语法
关键字.字段名
old和new并不是所有触发器都有
- insert 插入前为空，没有old
- delete 清除数据，没有new
商品自动扣减库存
delimiter $$ create trigger after_insert_order_trigger after insert on my_order for each row begin -- 通过new关键字获取新数据的id 和数量 update my_item set count = count - new.count where id = new.item_id; end $$ delimiter ;
触发触发器
mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | +----+---------+-------+ mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手机 | 99 | | 2 | 电脑 | 100 | | 3 | 包包 | 100 | +----+--------+-------+ insert into my_order (item_id, count) values(2, 3); mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | | 2 | 2 | 3 | +----+---------+-------+ mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手机 | 99 | | 2 | 电脑 | 97 | | 3 | 包包 | 100 | +----+--------+-------+
6.2 优化
如果库存数量没有商品订单多怎么办？
-- 删除原有触发器 drop trigger after_insert_order_trigger; -- 新增判断库存触发器 delimiter $$ create trigger after_insert_order_trigger after insert on my_order for each row begin -- 查询库存 select count from my_item where id = new.item_id into @count; -- 判断 if new.count > @count then -- 中断操作，暴力抛出异常 insert into xxx values ('xxx'); end if; -- 通过new关键字获取新数据的id 和数量 update my_item set count = count - new.count where id = new.item_id; end $$ delimiter ;
结果验证
mysql> insert into my_order (item_id, count) values(3, 101); ERROR 1146 (42S02): Table 'mydatabase2.xxx' doesn't exist mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | | 2 | 2 | 3 | +----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手机 | 99 | | 2 | 电脑 | 97 | | 3 | 包包 | 100 | +----+--------+-------+ 3 rows in set (0.00 sec)
