USE SQL2008SBS
GO

CREATE TRIGGER tiud_orderdetail ON Orders.OrderDetail
FOR INSERT, UPDATE, DELETE
AS

UPDATE a
SET a.FinalShipDate = c.FinalShipDate
FROM Orders.OrderHeader a INNER JOIN
    (SELECT od1.OrderID, MAX(od1.ShipDate) FinalShipDate
    FROM Orders.OrderDetail od1 INNER JOIN 
        (SELECT od2.OrderID
        FROM Orders.OrderDetail od2 INNER JOIN inserted i ON od2.OrderID = i.OrderID
        WHERE od2.ShipDate IS NOT NULL
        EXCEPT
        SELECT od3.OrderID
        FROM Orders.OrderDetail od3 INNER JOIN inserted i ON od3.OrderID = i.OrderID
        WHERE od3.ShipDate IS NULL) b 
    ON od1.OrderID = b.OrderID
    GROUP BY od1.OrderID) c 
ON a.OrderID = c.OrderID
GO