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 goods
FOR EACH ROW BEGIN
INSERT 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 goods
FOR EACH ROW BEGIN
DELETE 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 goods
FOR EACH ROW BEGIN
UPDATE detail SET price=new.price,jine=shuliang*(new.price) WHERE gid=(SELECT gid FROM goods WHERE  price=new.price);
END$$