Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: Trigger not inserting properly

    I have the following trigger that will insert the correctly if none of the items in the #tempKCS exist in the destination table. However if one of the items in the #tempKCS table do exist in the Sales table then the insert does not execute for the other itmes in the #temoKCS list that are not already listed in the Sales Table. Below is the trigger. Tell me where I am making my mistake. Thanks

    CREATE TRIGGER [Insert_KCSales] on [dbo].[Reclines]

    SELECT KitItemSum.Date, Kits.KitItemNo AS ItemNo, (Kits.Quantity * KitItemSum.QtySold) As QtySold, ((Kits.[Percentage]/100) * KitItemSum.AmtSold) AS AmtSold, KitItemSum.Division, 0 AS QtyReturned, 0 AS AmtReturned Into #TempKCS
    FROM (SELECT Summary.Recdate AS [Date], Summary.ItemNo, Sum(Summary.Qty) AS QtySold, SUM((Summary.Amount-Summary.DiscAmt)) AS AmtSold, Summary.Division
    FROM (SELECT DISTINCT R.Recno, R.Recdate,I.RecLineNo, I.ItemNo, I.Qty, I.Amount, I.DiscAmt, I.Division FROM Inserted I
    INNER JOIN Kits K ON I.ItemNo = K.MasterItemNo INNER JOIN Receipt R ON I.RecNo = R.RecNo INNER JOIN Items ON I.ItemNo = Items.ItemNo) AS Summary
    GROUP BY Summary.RecDate, Summary.ItemNo, Summary.Division) AS KitItemSum INNER JOIN Kits ON KitItemSum.ItemNo = Kits.MasterItemNo;

    INSERT INTO Sales ([Date], ItemNo, Division, QtySold, AmtSold, Qtyreturned, AmtReturned)
    SELECT [Date], ItemNo, Division, QtySold, Amtsold, Qtyreturned, Amtreturned
    FROM #TempKCS
    WHERE NOT EXISTS (SELECT S.[Date], S.[ItemNo] FROM Sales S INNER JOIN #TempKCS KCS on S.ItemNo = KCS.ItemNo AND S.[Date] = KCS.[Date]);
    Last edited by jstokes; 05-03-04 at 13:07.

  2. #2
    Join Date
    Mar 2004

    I have answered my own question

    The final INNER JOIN needed to be removed and replaced with a where modifier like the following:

    from #TempKCS KCS
    where not exists (SELECT S.[Date], S.[ItemNo] from Sales S WHERE S.ItemNo = KCS.ItemNo and S.[Date] = KCS.[Date]);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts