(一)、触发器的语法:
CREATE TRIGGER 触发器名
after / before (触时间)
insert / update / delete (触发条件)
ON
表名
FOR EACH ROW
BEGIN
触发事件
END;
(二)、触发器实验：
第一个触发器：
当用户购买商品时（也就是订单表增加订单数据时），商品表的该商品会自动减去用户购买的数量。
CREATE TRIGGER addOrder BEFORE insert ON ord FOR EACH ROW BEGIN
DECLARE goodsNum int;
select number into goodsNum from goods where gid=new.gid;
IF new.much > goodsNum THEN set new.much = goodsNum;
END IF;
IF new.much < 0 THEN set new.much = 0;
END IF;
update goods set number = number - new.much where gid = new.gid;
END;
第二个触发器：
当用户删除订单时，商品表的该商品会自动增加用户删除的订单数量。
CREATE TRIGGER delOrder before delete ON ord FOR EACH ROW
BEGIN
update goods set number = number + old.much where gid = old.gid;
END;
第三个触发器：
当用户修改订单数量时，商品表的该商品会自动减去或增加数量。
CREATE TRIGGER upOrder before update ON ord FOR EACH ROW
BEGIN
DECLARE goodsCount INT;
SELECT number INTO goodsCount FROM goods WHERE gid = new.gid;
IF new.much > goodsCount THEN set new.much = goodsCount;
END IF;
IF
new.much < 0 THEN set new.much = 0;
END IF;
update goods set number = number+old.much-new.much where gid=new.gid;
END;
