May 22, 2008 at 1:00 pm
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
May 26, 2008 at 12:18 pm
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
May 27, 2008 at 6:45 am
right you are
it's in the original trigger - that is not the reason it isn't working
May 27, 2008 at 8:58 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply