CREATE TRIGGER [dbo].[SalesDetails_Update_AdjustItemQty] > ON [dbo].[salesdetails] > AFTER INSERT >AS >BEGIN > -- SET NOCOUNT ON added to prevent extra result sets from > -- interfering with SELECT statements. > SET NOCOUNT ON; > >;with cteChanges AS (SELECT D.iItemid, SUM(COALESCE(D.iqty,0) - COALESCE(I.iqty,0)) as ChangedQty >FROM Deleted D INNER JOIN Inserted I on D.iItemid = I.iItemid >GROUP BY D.iItemid) > >MERGE dbo.Items as Trg >USING cteChanges as Src ON Trg.iItemId = Src.iItemid >WHEN MATCHED and Trg.iItemTypId = '43' >THEN UPDATE SET iQtyOnHand = COALESCE(Trg.iQtyOnHand,0) + ChangedQty; > >END >>
WHEN MATCHED and Trg.iItemTypId = '43'>
MERGE dbo.BranchItems as Trg >INNER JOIN Items on BranchItems.bri_itmfk = Items.iItemId >USING cteChanges as Src ON Trg.bri_pk = Src.iItemid >WHEN MATCHED and Items.iItemTypId = '43' >THEN UPDATE SET iQtyOnHand = COALESCE(Trg.iQtyOnHand,0) + ChangedQty;Post structures of your tables and what exactly do you want to update.