使用触发器做简单编程
有两张表(商品表和订单表,结构如下)
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