MySQL触发器增删改实例

表字段
商品表： goods（gid，gname，price）其属性是商品编码，商品名称，单价
销售表： detail （did，gid，price，shuliang，jine）其属性是销售单号，商品编号，单价，数量，金额

添加商品，自动向销售明细里添加数据

DELIMITER $$DROP TRIGGER IF EXISTS ins_detail_on_goods $$CREATE TRIGGER ins_detail_on_goods AFTER INSERT ON goodsFOR EACH ROW BEGININSERT detail(gid,price,shuliang,jine) VALUES(new.gid,new.price,0,0);END$$

删除商品，自动删除销售明细里的数据

DELIMITER $$DROP TRIGGER IF EXISTS del_detail_on_goods $$CREATE TRIGGER del_detail_on_goods AFTER DELETE ON goodsFOR EACH ROW BEGINDELETE FROM detail WHERE gid=old.gid;END$$

修改商品的单价，自动修改销售明细里对应商品的单价和金额

DELIMITER $$DROP TRIGGER IF EXISTS upd_detail_on_goods $$CREATE TRIGGER upd_detail_on_goods AFTER UPDATE ON goodsFOR EACH ROW BEGINUPDATE detail SET price=new.price,jine=shuliang*(new.price) WHERE gid=(SELECT gid FROM goods WHERE  price=new.price);END$$