cdn.BstElem_InsertTrigger_SEDKOMP

/****** Object:  Trigger [CDN].[BstElem_InsertTrigger_SEDKOMP]    Script Date: 09/07/2012 13:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Piotr Oslowski (SEDKOMP)
-- Create date: 2012-08-28
-- Description: TESTY
-- =============================================
ALTER TRIGGER [CDN].[BstElem_InsertTrigger_SEDKOMP]
   ON  [CDN].[BstElem]
   AFTER --INSERT,
   UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here


    DECLARE @BsnId INT
    DECLARE @TwrId INT
    DECLARE @MagId INT

    DECLARE @IloscMin DECIMAL(11,4)
    DECLARE @IloscMax DECIMAL(11,4)
    DECLARE @IloscZam DECIMAL(11,4)

    DECLARE @Stan DECIMAL(11,4)

    declare @Ilosc decimal(11,4)
    declare @IloscOry decimal(11,4)


    DECLARE kursor CURSOR FORWARD_ONLY FOR
    SELECT BsE_BsNID, BsE_TwrNumer, BsE_DoZamowienia FROM INSERTED
    WHERE BsESED_triggerFlaga is NULL

    OPEN kursor
    FETCH NEXT FROM kursor INTO @BsnId, @TwrId, @IloscOry
    While @@FETCH_STATUS = 0
        Begin

        --czytamy magazyn z nagłówka
        --UWAGA!!! bierzemy tylko jeden magazyn!!!
        SELECT TOP 1 @MagId = BsM_MagNumer FROM CDN.BstMagazyny WHERE BsM_BsNID = @BsnId AND BsM_MagTyp = 208 ORDER BY BsM_MagNumer

        SET @IloscMax = 0
        SET @IloscMin = 0
        SET @IloscZam = 0

        SELECT @IloscMax = Tpm_IloscMax, @IloscMin = Tpm_IloscMin, @IloscZam = Tpm_IloscZam FROM cdn.TwrParMag WHERE @TwrId = Tpm_TwrNumer AND @MagId = Tpm_MagNumer

        SET @IloscMax = IsNull(@IloscMax,0)
        SET @IloscMin = IsNUll(@IloscMin,0)
        SET @IloscZam = IsNull(@IloscZam,0)

        if @IloscZam = 0 BEGIN SET @IloscZam = 1 END

        SET @Stan = @IloscOry --[CDN].[sed_WezStanTowaru3](@MagId, @TwrId)

        SET @Stan = IsNull(@Stan,0)

        SET @Ilosc = @stan
        -- stan <= iloscmin



        --If (@Ilosc < @Iloscmax) AND (@IloscMax>@IloscMin) AND (@Ilosc>0)
        If (@IloscMax>@IloscMin) AND (@Ilosc>0)
            Begin

            --SET @Ilosc = @Stan + @IloscMax - @IloscMin
            --If (@Ilosc % @IloscZam) <> 0
            --  Begin
            --  SET @Ilosc = @Ilosc + (@IloscZam - @Ilosc % @IloscZam)
            --  End


            --Krok 1
            SET @Ilosc = @Ilosc + @IloscMax - @IloscMin

            --Krok 2

            if (@Ilosc % @IloscZam <> 0)
            Begin
                SET @Ilosc = (Floor(@Ilosc / @IloscZam)+1) * @IloscZam
            END




            UPDATE CDN.BstElem SET BsE_DoZamowienia = @Ilosc, BsESED_triggerFlaga = 1 WHERE BsE_BsNID = @BsnId AND BsE_TwrNumer = @TwrId


            End

        --If @Stan >= @IloscMin
        --  Begin
        --      SET @Ilosc = @Ilosc --0
        --  End





        --UPDATE CDN.BstElem SET BsE_DoZamowienia = @Ilosc WHERE BsE_BsNID = @BsnId AND
        --BsE_TwrNumer = @TwrId


        FETCH NEXT FROM kursor INTO @BsnId, @TwrId, @IloscOry

        END
    Close kursor
    DEALLOCATE kursor
END