Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Multi-Row update trigger

    Hi,

    I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

    DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

    DeliveryNote has the ReceivedDate
    DeliveryNoteDetail has the ReceivedQty

    I made the following trigger for handling single row updates, which works fine.

    UPDATE Purchasing.Product
    SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
    FROM Purchasing.DeliveryNote dn INNER JOIN
    Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
    inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
    Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
    Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

    Now I don't know how to handle multi-row situations when the same product is updated.
    Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Subqueries, perhaps...
    Code:
    UPDATE	Purchasing.Product
    SET	LastReceivedQty = subquery.ReceivedQty,
    	LastReceivedDate = subquery.ReceivedDate
    from	Purchasing.DeliveryNote dn
    	inner join --Subquery
    	    (SELECT	dnd.DeliveryNoteID,
    		    sum(i.ReceivedQty) ReceivedQty,
    		    max(dn.ReceivedDate) RecievedDate
    	    FROM	Purchasing.DeliveryNoteDetail dnd
    		    INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
    		    INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
    		    INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
    	    group by dnd.DeliveryNoteID) Subquery
    	    on dn.DeliveryNoteID = Subquery.DeliveryNoteID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •