有两张表(商品表和订单表,结构如下)
CREATE TABLE goods (
gid int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
num smallint(6) DEFAULT NULL
);
CREATE TABLE ord (
oid int(11) DEFAULT NULL,
gid int(11) DEFAULT NULL,
much smallint(6) DEFAULT NULL
);
插入数据:
INSERT INTO goods VALUES ('1','cat','32'), ('2','dog','65'), ('3','pig','21');
业务场景如下:
客户如果买的东西超过了库存(goods表的num字段),如何预防,能否在购买量(插入ord表的much字段)>库存量(goods表的num字段)时,把much自动改为num
使用触发器:
create trigger t
before
insert
on ord
for each row
begin
declare
rnum int;
select num into rnum from goods where gid = new.gid;
if new.much > rnum then
set new.much = rnum;
end if;
update goods set num = num - new.much where gid=new.gid;
end