✏️ 触发器练习 3
第 6 章 · 索引、视图和触发器 · 共 5 题 · 商品订单
🛠 建表与测试数据（练习前先执行）
sql
-- 准备数据
drop database if exists db_shop;
create database db_shop
default character set utf8
default collate utf8_general_ci;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`id` int(11) NOT NULL COMMENT '商品编号',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`num` int(11) NULL DEFAULT NULL COMMENT '商品库存',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, '可口可乐', 10);
INSERT INTO `goods` VALUES (2, '光明酸奶', 15);
INSERT INTO `goods` VALUES (3, '加多宝', 20);
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`oid` int(11) NOT NULL COMMENT '订单编号',
`gid` int(11) NULL DEFAULT NULL COMMENT '购买商品编号',
`amount` int(11) NULL DEFAULT NULL COMMENT '购买商品数量',
PRIMARY KEY (`oid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of orders
-- ----------------------------
SET FOREIGN_KEY_CHECKS = 1;
use db_shop;1. 为orders表创建触发器insert_orders_trigger，每次买家下单，在向订单中插入记录的同时自动更新商品表中的库存
查看参考答案（可复制）
sql
drop trigger if exists insert_orders_trigger;
create trigger insert_orders_trigger
after insert on orders
for each row
begin
update goods set num=num-new.amount where id=new.gid;
end;
insert into orders values (1,1,1);
select * from orders;
select * from goods;2. 为orders表创建触发器update_orders_trigger，修改orders表中的商品数量后，商品表goods中的商品数量自动编号
查看参考答案（可复制）
sql
drop trigger if exists update_orders_trigger;
create trigger update_orders_trigger
after update on orders
for each row
begin
update goods set num = num+old.amount-new.amount where id=new.gid;
end;
update orders set amount=5 where oid=1;
select * from goods;3. 为orders表创建一个触发器delete_orders_trigger，删除orders表中的记录后，goods表中的商品数量自动变化
查看参考答案（可复制）
sql
drop trigger if exists delete_orders_trigger;
create trigger delete_orders_trigger
after delete on orders
for each row
begin
update goods set num=num+old.amount where id=old.gid;
end;
delete from orders where oid=1;
select * from goods;4. 为orders表创建before触发器insert_orders_trigger2，以规避库存不足的问题
查看参考答案（可复制）
sql
drop trigger if exists insert_orders_trigger2;
create trigger insert_orders_trigger2
before insert on orders
for each row
begin
declare msg varchar(200);
update goods set num=num-new.amount where id=new.gid and num>=new.amount;
if row_count()<>1 then
select concat(name,'库存不足') into msg from goods where id=new.gid;
signal sqlstate 'TX000' set message_text=msg;
end if;
end;
drop trigger if exists insert_orders_trigger3;
create trigger insert_orders_trigger3
before insert on orders
for each row
begin
declare msg varchar(200);
update goods set num=num-new.amount where id=new.gid and num>=new.amount;
if row_count()<>1 then
select concat(name,'库存不足') into msg from goods where id=new.gid;
signal sqlstate 'TX000' set message_text=msg;
end if;
end;
-- row_count()函数记录更新操作影响的行数，如果其值不等于1，表明goods表没有更新，意味着订单中的商品数量超过了库存数量
-- singal用于在存储程序中向调用者返回错误信息。此外，它还能提供错误编号、sqlstate值、错误消息等
insert into orders(oid,gid,amount) values (2,2,18);5. 用图形化界面创建触发器
查看参考答案（可复制）
sql