B2B产品触发器

写入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