Use a trigger to update data in another table when deleting transaction

Question

I have 2 tables:

• tblStock (PartNo, StockQty, StockLocation)
• tblTransfer (PartNo, StockQty, FromLocation, ToLocation)

When I delete data in tblTransfer, the stock is reversed back to tblStock.

The insert trigger is working fine, but the delete trigger is not working.

Please see my delete trigger:

CREATE TRIGGER [dbo].[UpdateStock] 
ON [dbo].[tblTransfer]
FOR DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    MERGE tblStock s
    USING (SELECT d.PartNo, d.StockLocation
           FROM Deleted d) MergeData ON s.PartNo = MergeData.PartNo
                                     AND s.StockLocation = MergeData.FromLocation
      WHEN MATCHED THEN
        UPDATE SET
            s.StockQty = s.StockQty - MergeData.StockQty
            s.StockLocation = MergeData.ToLocation
END;

Answer 1

    create trigger trg_test
    on    dbo.tbltransfer
    after delete 
    as
    begin

    update tbl
    set  tbl.stockqnty=d.quantity, tbl.stocklocation=d.location
    from
    tblstock tbl
    join
    deleted d
    on d.partno=tbl.partno

    end