Fork me on GitHub

使用触发器做简单编程

有两张表(商品表和订单表,结构如下)

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

  

 

posted @ 2014-11-23 01:12  龙族小龙  阅读(384)  评论(0编辑  收藏  举报