B2B产品触发器
2021-11-22 本文已影响0人
yichen_china
写入sku表触发
delimiter //
DROP TRIGGER IF EXISTS trigger_insert_zt_sm_stock;
CREATE TRIGGER trigger_insert_zt_sm_stock BEFORE INSERT ON zt_sm_stock
FOR EACH ROW
BEGIN
SET @defaultAttr="规格";
SET @defaultAttrValue="默认";
set @skuId = new.sku_id;
set @productId=null;
set @sku=null;
set @image =null;
select id,IFNULL(sku,@defaultAttrValue),IFNULL(image,"") into @productId,@sku,@image from yx_store_product WHERE yx_store_product.bar_code =@skuId LIMIT 1;
IF @productId IS NOT null then
set @result =CONCAT('{"attr":[{"attrHidden":"","detail":["',@defaultAttr,'"],"detailValue":"","value":"', @sku,'"}],"value":[{"barCode":"',@skuId,'","brokerage":0.0,"brokerageTwo":0.0,"cost":',new.plus_price,',"detail":{"',@defaultAttrValue,'":"', @sku,'"},"integral":0,"otPrice":',new.price,',"pic":"',@image,'","pinkPrice":',new.price,',"pinkStock":0',',"price":',new.price,',"seckillPrice":0,"seckillStock":0,"stock":',new.stock,',"value1":"规格","volume":0,"weight":0}]}');
insert ignore INTO yx_store_product_attr_result(product_id,change_time,result) VALUES(@productId,now(),@result);
insert ignore into yx_store_product_attr(product_id,attr_name,attr_values) VALUES(@productId,@defaultAttr,@defaultAttrValue);
insert ignore into yx_store_product_attr_value(product_id,sku,stock,price,vip_price,image,`unique`,bar_code,ot_price,cost,weight,volume)VALUES(@productId,@sku,new.stock,new.price,new.plus_price,@image,@skuId,@skuId,price,new.cost_price,0,0);
-- ELSE
-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "product_id not null";
END IF;
END//
delimiter ;
show triggers;
更新sku表触发
delimiter //
CREATE TRIGGER trigger_update_zt_sm_stock BEFORE UPDATE ON zt_sm_stock
FOR EACH ROW
BEGIN
SET @defaultAttr="规格";
SET @defaultAttrValue="默认";
set @skuId = new.sku_id;
set @productId=null;
set @sku="";
set @image ="";
set @result="";
select id,IFNULL(sku,@defaultAttrValue),IFNULL(image,"") into @productId,@sku,@image from yx_store_product WHERE yx_store_product.bar_code =@skuId LIMIT 1;
IF @productId IS NOT null then
set @result =CONCAT('{"attr":[{"attrHidden":"","detail":["',@defaultAttr,'"],"detailValue":"","value":"',@sku,'"}],"value":[{"barCode":"',@skuId,'","brokerage":0.0,"brokerageTwo":0.0,"cost":',new.plus_price,',"detail":{"',@defaultAttrValue,'":"', @sku,'"},"integral":0,"otPrice":',new.price,',"pic":"',@image,'","pinkPrice":',new.price,',"pinkStock":0',',"price":',new.price,',"seckillPrice":0,"seckillStock":0,"stock":',new.stock,',"value1":"规格","volume":0,"weight":0}]}');
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore INTO yx_store_product_attr_result(product_id,change_time,result) VALUES(@productId,now(),@result);
else
UPDATE ignore yx_store_product_attr_result SET change_time=now(),result=@result WHERE product_id=@productId;
end if;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr(product_id,attr_name,attr_values) VALUES(@productId,@defaultAttr,@defaultAttrValue);
else
UPDATE ignore yx_store_product_attr SET attr_name=@defaultAttr,attr_values=@defaultAttrValue WHERE product_id=@productId;
end if;
UPDATE ignore yx_store_product SET validity_date=new.validity_date,stock=new.stock,price=new.price,vip_price=new.plus_price WHERE id=@productId;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr_value(product_id,sku,stock,price,vip_price,image,`unique`,bar_code,ot_price,cost,weight,volume)VALUES(@productId,@sku,new.stock,new.price,new.plus_price,@image,@skuId,@skuId,price,new.cost_price,0,0) ON DUPLICATE KEY UPDATE cost=VALUES(cost),sku=VALUES(sku),stock=VALUES(stock),vip_price=VALUES(vip_price),price=VALUES(price),ot_price=VALUES(ot_price);
else
UPDATE ignore yx_store_product_attr_value SET sku=@sku,stock=new.stock,image=@image,price=new.price,vip_price=new.plus_price,cost=new.cost_price WHERE product_id=@productId;
end if;
-- ELSE
-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "product_id not null";
END IF;
END//
delimiter ;
show triggers;
B2B ERP订单完成触发
delimiter //
CREATE TRIGGER trigger_update_sm_order_deliver_detail BEFORE UPDATE ON sm_order_deliver_detail
FOR EACH ROW
BEGIN
SET @order_num= NEW.order_num;
update ignore zt_store_order set shipping_status=1,audit_status=1,status=1 WHERE order_id = @order_num;
END
商品信息表触发器
delimiter //
CREATE TRIGGER trigger_update_after_zt_product_info BEFORE UPDATE ON zt_product_info
FOR EACH ROW
BEGIN
SET @defaultAttr="规格";
SET @defaultAttrValue="默认";
set @skuId = new.product_id;
set @productId=null;
set @sku=new.spec;
set @image =new.first_image;
set @result="";
-- 更新相关表正文 下文会重复使用，修改时候只需复制即可
select id,IFNULL(sku,@defaultAttrValue),IFNULL(image,"") into @productId,@sku,@image from yx_store_product WHERE yx_store_product.bar_code =@skuId LIMIT 1;
IF @productId IS NOT null then
set @result =CONCAT('{"attr":[{"attrHidden":"","detail":["',@defaultAttr,'"],"detailValue":"","value":"',@sku,'"}],"value":[{"barCode":"',@skuId,'","brokerage":0.0,"brokerageTwo":0.0,"cost":',new.plus_price,',"detail":{"',@defaultAttrValue,'":"', @sku,'"},"integral":0,"otPrice":',new.price,',"pic":"',@image,'","pinkPrice":',new.price,',"pinkStock":0',',"price":',new.price,',"seckillPrice":0,"seckillStock":0,"stock":',new.stock,',"value1":"规格","volume":0,"weight":0}]}');
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore INTO yx_store_product_attr_result(product_id,change_time,result) VALUES(@productId,now(),@result);
else
UPDATE ignore yx_store_product_attr_result SET change_time=now(),result=@result WHERE product_id=@productId;
end if;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr(product_id,attr_name,attr_values) VALUES(@productId,@defaultAttr,@defaultAttrValue);
else
UPDATE ignore yx_store_product_attr SET attr_name=@defaultAttr,attr_values=@defaultAttrValue WHERE product_id=@productId;
end if;
UPDATE ignore yx_store_product SET stock=new.stock,price=new.price,vip_price=new.plus_price WHERE id=@productId;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr_value(product_id,sku,stock,price,vip_price,image,`unique`,bar_code,ot_price,cost,weight,volume)VALUES(@productId,@sku,new.stock,new.price,new.plus_price,@image,@skuId,@skuId,price,new.cost_price,0,0) ON DUPLICATE KEY UPDATE cost=VALUES(cost),sku=VALUES(sku),stock=VALUES(stock),vip_price=VALUES(vip_price),price=VALUES(price),ot_price=VALUES(ot_price);
else
UPDATE ignore yx_store_product_attr_value SET sku=@sku,stock=new.stock,image=@image,price=new.price,vip_price=new.plus_price,cost=new.cost_price WHERE product_id=@productId;
end if;
ELSE
-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "product_id not null";
insert ignore into yx_store_product(goods_no,store_name,keyword,bar_code,approval_no,factory_name,is_rx,gsp_type,stock,vip_price,store_info,sku,unit_name,description,slider_image,image) VALUES(new.product_id,new.product_name,new.product_name,new.product_id,new.approval_no,new.factory_name,new.prescription_sales_type,new.gsp_type,new.stock,new.plus_price,new.effect,new.spec,new.unit,new.indications,new.images,new.first_image);
-- 更新相关表正文 下文会重复使用，修改时候只需复制即可
select id,IFNULL(sku,@defaultAttrValue),IFNULL(image,"") into @productId,@sku,@image from yx_store_product WHERE yx_store_product.bar_code =@skuId LIMIT 1;
IF @productId IS NOT null then
set @result =CONCAT('{"attr":[{"attrHidden":"","detail":["',@defaultAttr,'"],"detailValue":"","value":"',@sku,'"}],"value":[{"barCode":"',@skuId,'","brokerage":0.0,"brokerageTwo":0.0,"cost":',new.plus_price,',"detail":{"',@defaultAttrValue,'":"', @sku,'"},"integral":0,"otPrice":',new.price,',"pic":"',@image,'","pinkPrice":',new.price,',"pinkStock":0',',"price":',new.price,',"seckillPrice":0,"seckillStock":0,"stock":',new.stock,',"value1":"规格","volume":0,"weight":0}]}');
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore INTO yx_store_product_attr_result(product_id,change_time,result) VALUES(@productId,now(),@result);
else
UPDATE ignore yx_store_product_attr_result SET change_time=now(),result=@result WHERE product_id=@productId;
end if;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr(product_id,attr_name,attr_values) VALUES(@productId,@defaultAttr,@defaultAttrValue);
else
UPDATE ignore yx_store_product_attr SET attr_name=@defaultAttr,attr_values=@defaultAttrValue WHERE product_id=@productId;
end if;
UPDATE ignore yx_store_product SET stock=new.stock,price=new.price,vip_price=new.plus_price WHERE id=@productId;
if not exists (SELECT 1 FROM `yx_store_product_attr_result` WHERE product_id =@productId LIMIT 1) THEN
insert ignore into yx_store_product_attr_value(product_id,sku,stock,price,vip_price,image,`unique`,bar_code,ot_price,cost,weight,volume)VALUES(@productId,@sku,new.stock,new.price,new.plus_price,@image,@skuId,@skuId,price,new.cost_price,0,0) ON DUPLICATE KEY UPDATE cost=VALUES(cost),sku=VALUES(sku),stock=VALUES(stock),vip_price=VALUES(vip_price),price=VALUES(price),ot_price=VALUES(ot_price);
else
UPDATE ignore yx_store_product_attr_value SET sku=@sku,stock=new.stock,image=@image,price=new.price,vip_price=new.plus_price,cost=new.cost_price WHERE product_id=@productId;
end if;
END IF;
END IF;
END
delimiter ;
商品信息表
insert ignore into yx_store_product(goods_no,store_name,keyword,bar_code,approval_no,factory_name,is_rx,gsp_type,stock,vip_price,store_info,sku,unit_name,indications,images,frist_image) VALUES(product_id,product_name,store_name,product_id,approval_no,factory_name,prescription_sales_type,gsp_type,new.stock,new.plus_price,effect,spec,unit,indications,images,frist_image);
批量导入商品属性表
insert INTO yx_store_product_attr_result(product_id,change_time,result) SELECT product_id,now() change_time,CONCAT('{"attr":[{"attrHidden":"","detail":["默认"],"detailValue":"","value":"',sku,'"}],"value":[{"barCode":"',bar_code,'","brokerage":0.0,"brokerageTwo":0.0,"cost":',vip_price,',"detail":{"规格":"',sku,'"},"integral":0,"otPrice":',ot_price,',"pic":"',image,'","pinkPrice":',price,',"pinkStock":0',',"price":',price,',"seckillPrice":0,"seckillStock":0,"stock":',stock,',"value1":"规格","volume":',volume,',"weight":',weight,'}]}') as result FROM yx_store_product_attr_value
根据通用名归档商品分类
UPDATE yx_store_product a,test_type_name b,yx_store_category c SET a.cate_id = c.id WHERE a.keyword =b.current_name and c.cate_name=b.type_name_two and c.id is not null
