(一)辉川、觸發(fā)器的語法:
CREATE TRIGGER 觸發(fā)器名
after / before (觸時(shí)間)
insert / update / delete (觸發(fā)條件)
ON
表名
FOR EACH ROW
BEGIN
觸發(fā)事件
END;
(二)、觸發(fā)器實(shí)驗(yàn)：
第一個(gè)觸發(fā)器：
當(dāng)用戶購買商品時(shí)（也就是訂單表增加訂單數(shù)據(jù)時(shí)）矗钟，商品表的該商品會(huì)自動(dòng)減去用戶購買的數(shù)量痒给。
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;
第二個(gè)觸發(fā)器：
當(dāng)用戶刪除訂單時(shí)贪薪，商品表的該商品會(huì)自動(dòng)增加用戶刪除的訂單數(shù)量。
CREATE TRIGGER delOrder before delete ON ord FOR EACH ROW
BEGIN
update goods set number = number + old.much where gid = old.gid;
END;
第三個(gè)觸發(fā)器：
當(dāng)用戶修改訂單數(shù)量時(shí)胖笛，商品表的該商品會(huì)自動(dòng)減去或增加數(shù)量网持。
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;
