-- =============================================-- Author: zhang.jf 兔子-- Create date: 2015.9.1-- Description: /* 1、初始导入门店库存(计算出Primium), 同城市、同经销商下的城市补货库存为:SUM(Primium)-Sum(FirstQuanty) 2、门店调整 2.1 门店批量调整primium,FirstQuanty不做相应的变化 2.2 单个门店调整 FirstQuanty不能大于Primium 3、兑换 --库存减少 4、补货 --城市经销商库存 减少,门店库存 新增 */--alter table ProductReceiveRecord disable trigger [tr_ProductReceiveRecord] --禁用--alter table ProductReceiveRecord enable trigger [tr_ProductReceiveRecord] --启用-- 测试:/*INSERT INTO ProductReceiveRecord( CampaignId , CityId , Gift_DistributorId , ProductGiftId , ShopCode , Premium, Quantity, TransactionType, CreateUser )SELECT 100 , 100 , 100 , 100 , '100' , 20 , 2 , 4, 100;SELECT * FROM ProductReceiveRecord WHERE CampaignId = 100; SELECT * FROM ProductInventory WHERE CampaignId = 100; DELETE ProductReceiveRecord WHERE CampaignId = 100;DELETE ProductInventory WHERE CampaignId = 100; */-- =============================================IF OBJECT_ID( '[tr_prr_pi]' , 'TR' )IS NOT NULL BEGIN DROP TRIGGER tr_prr_pi; END;GOCREATE TRIGGER tr_prr_pi ON ProductReceiveRecord FOR INSERT , DELETEASBEGIN --SELECT * FROM Product --RAISERROR('test',16,10) --1、新增记录 IF EXISTS( SELECT TOP 1 1 FROM INSERTED ) BEGIN SELECT CampaignId , CityId , Gift_DistributorId , ShopCode , ProductGiftId , TransactionType , SUM( Premium )Premium , SUM(FirstQuantity) FirstQuantity, MAX( CreateUser )CreateUser , SUM( Quantity )Quantity INTO #t1 FROM inserted GROUP BY CampaignId , CityId , Gift_DistributorId , ShopCode , ProductGiftId , TransactionType; --2、判断是交易记录产生后是否存在库存小于0的情况,如果存在则回滚事务 IF EXISTS( SELECT TOP 1 1 FROM #t1 a LEFT JOIN ProductInventory b WITH ( NOLOCK )ON a.ProductGiftId = b.ProductId AND a.CampaignId = b.CampaignId AND a.CityId = b.CityId AND a.ShopCode = b.ShopCode AND a.Gift_DistributorId = b.Gift_DistributorId WHERE a.Quantity + ISNULL( b.RepsProductCount , 0 ) < 0 ) BEGIN RAISERROR( '库存不足' , 16 , 16 ); ROLLBACK TRAN; END; ELSE BEGIN --非首次交易记录,更新库存记录 UPDATE b SET b.RepsProductCount = b.RepsProductCount + a.Quantity , --首发量 b.FirstQuantity = CASE WHEN dbo.fn_getTransition( a.TransactionType )IN( '入库' , '调整' )THEN b.FirstQuantity + a.FirstQuantity ELSE b.FirstQuantity END , --Premium b.Premium = CASE WHEN dbo.fn_getTransition( a.TransactionType )IN( '入库' , '调整' )THEN b.Premium + a.Premium ELSE b.Premium END , b.UpdateTime = GETDATE( ) , b.UpdateId = a.CreateUser , b.Remark = dbo.fn_getTransition( a.TransactionType ) FROM #t1 a JOIN ProductInventory b WITH ( NOLOCK ) ON a.ProductGiftId = b.ProductId AND a.CampaignId = b.CampaignId AND a.CityId = b.CityId AND a.ShopCode = b.ShopCode AND a.Gift_DistributorId = b.Gift_DistributorId; --首次交易记录,添加新的库存记录 INSERT INTO ProductInventory( CampaignId , ProductId , CityId , ShopCode , Gift_DistributorId , DistributorId , Premium , FirstQuantity , RepsProductCount , CreateUser ) SELECT a.CampaignId , a.ProductGiftId , a.CityId , a.ShopCode , a.Gift_DistributorId , a.Gift_DistributorId , a.Premium , a.FirstQuantity , a.Quantity , a.CreateUser FROM #t1 a LEFT JOIN ProductInventory b ON a.ProductGiftId = b.ProductId AND a.CampaignId = b.CampaignId AND a.CityId = b.CityId AND a.ShopCode = b.ShopCode AND a.Gift_DistributorId = b.DistributorId WHERE b.Id IS NULL; END; END; IF EXISTS( SELECT TOP 1 1 FROM deleted ) BEGIN --汇总数据，避免重复数据更新不准确问题 SELECT CampaignId , CityId , Gift_DistributorId , ShopCode , ProductGiftId , MAX( CreateUser )CreateUser , SUM( Quantity )Quantity INTO #t2 FROM deleted GROUP BY CampaignId , CityId , Gift_DistributorId , ShopCode , ProductGiftId; --判断是交易记录产生后是否存在库存小于0的情况,如果存在则回滚事务 IF EXISTS( SELECT TOP 1 1 FROM #t2 a JOIN ProductInventory b ON a.ProductGiftId = b.ProductId AND a.CampaignId = b.CampaignId AND a.CityId = b.CityId AND a.ShopCode = b.ShopCode AND a.Gift_DistributorId = b.Gift_DistributorId WHERE b.RepsProductCount - a.Quantity < 0 ) BEGIN RAISERROR( '删除操作--库存不足' , 16 , 16 ); ROLLBACK TRANSACTION; END; ELSE BEGIN --删除记录后，更新库存记录 UPDATE ProductInventory SET RepsProductCount = RepsProductCount - a.Quantity , UpdateId = a.CreateUser , UpdateTime = GETDATE( ) FROM #t2 a JOIN ProductInventory b ON a.ProductGiftId = b.ProductId AND a.CampaignId = b.CampaignId AND a.CityId = b.CityId AND a.ShopCode = b.ShopCode AND a.Gift_DistributorId = b.Gift_DistributorId; END; END;END;