提示:以下是本篇文章正文内容,下面案例可供参考
一、建立基础表
create table goods( #商品表
gid char(10), #商品编号
number int #库存数目
);
create table order_detail( #订单表
gid char(10),
number int, #下单数目
ordertime timestamp
);
insert into goods values("P1",100),("P2",100);
insert into order_detail values("P1",100,"2020-01-20 12:33:33"),("P2",100,"2020-01-20 12:33:33");
二、操作步骤
1.建立一个触发器,当订货时,会判断库存是否够,如果够,则订货信息会顺利的写入order_detail表,
库存信息更新,如果该商品库存不够,报错"商品库存不足"
代码如下(示例):
create trigger p_i_order_detail before insert on order_detail for each ROW
BEGIN
declare f int;
select number into f from goods where gid=new.gid;
if f>new.number then update goods set number=number-new.number where gid=new.gid;
else SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "库存不足",MYSQL_ERRNO = 1333;
end if;
end
2.建立一个触发器,如果商品表商品编号变了,订单表商品编号随之改变
代码如下(示例):
CREATE TRIGGER pass AFTER UPDATE ON goods FOR EACH ROW
BEGIN
if (old.gid != new.gid)
THEN
UPDATE order_detail set gid = new.gid WHERE gid = old.gid;
end if;
end
drop TRIGGER pass
写出2后,测试下订单(往order_detail表中插入数据):gid=“p1”,number=10 ,就能成功了。
INSERT INTO order_detail(gid,number) VALUES ("P1",10);