CTE in trigger not working

  • Seggerman-675349

    Hall of Fame

    Points: 3563

    I have a cascading trigger which is supposed to continue adding records conditionally

    only problem, it adds records - with mostly NULLs, when the CTE returns no rows

    let me try and include code

    CREATE TRIGGER trInsert

    FOR INSERT AS

    BEGIN

    WITH NewInvoiceItems(StoreID, SKUMID, Quantity) AS

    (

    SELECT I.StoreID, PBSKM.SKUMID,

    SUM(CAST(NegativeQuantity + INS.Quantity AS INT)) AS Quantity

    FROM tblInventory I

    INNER JOIN tblPBSKM PBSKM

    ON I.SKUMID = PBSKM.SKUMID

    INNER JOIN INSERTED INS

    ON PBSKM.PBSKMID = INS.PBSKMID

    INNER JOIN tblInvoices IV

    ON I.StoreID = IV.StoreID

    AND INS.InvoiceID = IV.InvoiceID

    INNER JOIN tblStore S

    ON I.StoreID = S.StoreID

    WHERE (INS.SKUID IS NOT NULL)

    AND (S.UpdateOnHandOnInvoiceReceipt = 'T')

    GROUP BY I.StoreID, PBSKM.SKUMID

    )

    UPDATE tblInventory

    SET OnHand = ISNULL(OnHand,0) + Quantity,

    InvReasonCodeID = 1,

    DateModified = @RightNow,

    EmployeeID_ModifiedBy = 1

    FROM tblInventory I

    INNER JOIN NewInvoiceItems NII

    ON I.StoreID = NII.StoreID

    AND I.SKUMID = NII.SKUMID

    END

    tblInventory is being updated even if NewInvoiceItems returns no rows because UpdateOnHandOnInvoiceReceipt on the Store table is 'F'

    which in turn generates records for an update log with NULLs in everything

    I thought the INNER JOIN would prevent that

  • Adam Haines

    SSC-Insane

    Points: 23197

    Did you forget to post the on clause of the trigger, or do you not have one?

    For the trigger part why dont you peform a check before running the CTE?

    CREATE TRIGGER trInsert

    FOR INSERT AS

    ON SOMETABLE -- i added this part

    BEGIN

    IF EXISTS (SELECT 1 FROM tblInventory I

    INNER JOIN tblPBSKM PBSKM

    ON I.SKUMID = PBSKM.SKUMID

    INNER JOIN INSERTED INS

    ON PBSKM.PBSKMID = INS.PBSKMID

    INNER JOIN tblInvoices IV

    ON I.StoreID = IV.StoreID

    AND INS.InvoiceID = IV.InvoiceID

    INNER JOIN tblStore S

    ON I.StoreID = S.StoreID

    WHERE (INS.SKUID IS NOT NULL)

    AND (S.UpdateOnHandOnInvoiceReceipt = 'T')

    GROUP BY I.StoreID, PBSKM.SKUMID)

    BEGIN

    ;WITH NewInvoiceItems(StoreID, SKUMID, Quantity) AS

    (

    SELECT I.StoreID, PBSKM.SKUMID,

    SUM(CAST(NegativeQuantity + INS.Quantity AS INT)) AS Quantity

    FROM tblInventory I

    INNER JOIN tblPBSKM PBSKM

    ON I.SKUMID = PBSKM.SKUMID

    INNER JOIN INSERTED INS

    ON PBSKM.PBSKMID = INS.PBSKMID

    INNER JOIN tblInvoices IV

    ON I.StoreID = IV.StoreID

    AND INS.InvoiceID = IV.InvoiceID

    INNER JOIN tblStore S

    ON I.StoreID = S.StoreID

    WHERE (INS.SKUID IS NOT NULL)

    AND (S.UpdateOnHandOnInvoiceReceipt = 'T')

    GROUP BY I.StoreID, PBSKM.SKUMID

    )

    UPDATE tblInventory

    SET OnHand = ISNULL(OnHand,0) + Quantity,

    InvReasonCodeID = 1,

    DateModified = @RightNow,

    EmployeeID_ModifiedBy = 1

    FROM tblInventory I

    INNER JOIN NewInvoiceItems NII

    ON I.StoreID = NII.StoreID

    AND I.SKUMID = NII.SKUMID

    END

    END

  • Seggerman-675349

    Hall of Fame

    Points: 3563

    right you are

    it's in the original trigger - that is not the reason it isn't working

  • Vladan

    SSC-Insane

    Points: 21885

    Did you try to rewrite the trigger with derived table instead of CTE? Did it work correctly that way?

    I'm not sure whether CTE work properly with triggers (I'm comparatively new to SQLS2005), especially when the join to INSERTED/DELETED tables is done inside the CTE... so I would first try whether it works without CTE.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply