>>Hi,
>>
>>I have this trigger:
>>
>>

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
>>

>>
>>Which updates the iQtyOnHand of the Items table when a sale is recorded n the SalesDetails table. It's been working fine for a number of years.
>>
>>I am now re-designing my database so that instead of having just an Items table I have a BranchItems table so that I can manage stock of items at multiple branches,
>>
>>So I have created a BranchItems table which has a PK and 2 foreign keys one to the Items table and the other to the Branches table and the QtyOnHand field.
>>
>>My issue with changing the above trigger is the reference to iItemTypId in this line:
>>
>>

WHEN MATCHED and Trg.iItemTypId = '43'

>>
>>That column is in the Items table not the BranchItems table. Is there a way to do a join in the MERGE statement? If not how would I re-write this code?
>>
>>This was my initial attempt, but it gives a syntax error (incorrect syntax near 'INNER'. Expecting USING):
>>
>>

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.
>
>The JOIN needs to go to the cteChanges instead of the target table. Target table is the one you're going to modify using source which can be a query.

Thanks that gave me the push I needed.

I changed it to this:

;with cteChanges AS (SELECT D.iItemid, SUM(COALESCE(D.iqty,0) - COALESCE(I.iqty,0)) as ChangedQty, Items.iItemTypId
FROM Deleted D INNER JOIN Inserted I on D.iItemid = I.iItemid
INNER JOIN Items ON D.iItemId = Items.iItemId
GROUP BY D.iItemid, Items.iItemTypId)

MERGE dbo.BranchItems as Trg
USING cteChanges as Src ON Trg.bri_pk = Src.iItemid
WHEN MATCHED and Src.iItemTypId = '43' 
THEN UPDATE SET bri_qtyonhand = COALESCE(Trg.bri_qtyonhand,0) + ChangedQty;

Adding in the INNER JOIN Items to the cteChanges