Mysql触发器实例

查看触发器
SELECT * from information_schema.`TRIGGERS`;

删除触发器
drop TRIGGER generate_GoodsInfo;

创建触发器
CREATE TRIGGER generate_GoodsInfo AFTER INSERT ON price FOR EACH ROW
BEGIN
declare user_type VARCHAR(10);
SELECT level into user_type from user_info;
IF user_type='5' THEN
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.region_price,0);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.market_price,new.region_price,1);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.city_price,new.region_price,2);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.prov_price,new.region_price,3);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.agent_price,new.region_price,4);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.region_price,new.region_price,5);
ELSEIF user_type='4' THEN
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.agent_price,0);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.market_price,new.agent_price,1);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.city_price,new.agent_price,2);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.prov_price,new.agent_price,3);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.agent_price,new.agent_price,4);
ELSEIF user_type='3' THEN
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.prov_price,0);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.market_price,new.prov_price,1);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.city_price,new.prov_price,2);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.prov_price,new.prov_price,3);
ELSEIF user_type='2' THEN
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.city_price,0);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.market_price,new.city_price,1);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.city_price,new.city_price,2);
ELSEIF user_type='1' THEN
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.market_price,0);
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.market_price,new.market_price,1);
ELSE
INSERT INTO goods VALUES(DEFAULT,new.goods_name,new.model,new.retail_price,new.retail_price,0);
END IF;
END;

转载于:https://my.oschina.net/royweb/blog/781989