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;