GoldHoo/doc/调整记录/已更新正式环境/2020-12-07 11.15.sql

USE [befri_Goldhoo]
GO
/****** Object:  Trigger [clearance].[tri_ClearanceAccDetail_INSERT]    Script Date: 2020/12/7 10:10:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER Discount.[tri_DiscountAccDetail_INSERT] ON Discount.DiscountAccDetail
    FOR INSERT
AS
    BEGIN

        SET NOCOUNT ON;
-- =============================================
-- 开发人员: You
-- 开发日期: 20201207
-- 功能描述: 当插入记录时自动更新折扣总帐
-- =============================================
        MERGE Discount.DiscountAcc AS t
            USING INSERTED AS s
            ON ( s.WarehouseId = t.WarehouseId
                 AND s.InventoryId = t.InventoryId
               )
            WHEN MATCHED
                THEN			
				UPDATE
                    SET Quantity = Quantity + s.IncreasedQty - s.DecreasedQty ,
                        [DiscountCoef] = CASE WHEN s.DecreasedQty > 0
                                              THEN t.[DiscountCoef]
                                              ELSE s.[DiscountCoef]
                                         END
            WHEN NOT MATCHED
                THEN
				INSERT  (
                          WarehouseId ,
                          InventoryId ,
                          Quantity ,
                          [DiscountCoef]
                        )
                    VALUES
                        ( s.WarehouseId ,
                          s.InventoryId ,
                          s.IncreasedQty - s.DecreasedQty ,
                          s.DiscountCoef
                        );
    END