触发器

-- =============================================
-- 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;
GO
CREATE TRIGGER tr_prr_pi ON ProductReceiveRecord
    FOR INSERT , DELETE
AS
BEGIN
    --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;