>>>Hi Naomi
>>>Thanks for helping. I am using version 2005.
>>>Yes, it's one Activity record for each date/tour combination.
>>>Changing Dates or TourID is allowed in the Orders table, that's why made things too complicated for me.
>>>If an activity record has lost all his reservations, it can be deleted...
>>>
>>>Jaime
>>>
>>
>>I see - then it's more complex indeed and perhaps it may be better solved with a trigger (using UPDATE check).
>>
>>I need to run now, post your attempts in the meantime and I'll help you when I return.
>
>Hi Naomi
>Checked triggers help. I agree the function should be triggered upon insert / update , but it should run the same code in all cases.
>The way i see the function should be : (no matter if after insert or update):
>1) looks up if specific date/tour combination already exists in the Activity table. If not found, we will use an INSERT command, otherwise, we will use an UPDATE.
>2) generate the new Activity line, i.e select from Orders group by date/tour for specific date/tour
>3) run the command from 1) with the data from 2)
>Read a lot about triggers and stored procs, but still, i'm not at the level where i can do this all by myself :-(
>
>Jaime

DECLARE @Activ table (ActivityID int, [Date] datetime, Tour_ID int)
IF OBJECT_ID('TempDB..#TempActiv',N'U') IS NOT NULL DROP TABLE #TempActiv

BEGIN TRANSACTION
-- Insert or update order - assume we know the @OrderID
select [Date],[Tour_ID], Sum(Pax) as TotalPax INTO #TempActiv from dbo.Orders 
WHERE OrderID = @OrderID
GROUP BY  [Date],Tour_ID

UPDATE Activity SET TotalPax = TA.TotalPax from dbo.Activity INNER JOIN #TempActiv TA ON Activity.[Date] = TA.[Date]
AND Activity.[Tour_ID] = TA.[Tour_ID]
OUTPUT Inserted.Activity_ID, Inserted.[Date], Inserted.[Tour_ID] INTO @Activ
IF @@ROWCOUNT = 0 -- this is new date/tour_id
   INSERT INTO Activity ([Date],[Tour_ID], TotalPax)
   OUTPUT Inserted.ActivityID, Inserted.[Date], Inserted.[Tour_ID] into @Activ
  SELECT * from #TempActiv
 
UPDATE Orders  SET Activity_ID = A.Activity_ID from dbo.Orders INNER JOIN @Activ A
ON Orders.[Date] = A.[Date] and Orders.[Tour_ID] = A.[Tour_ID]
WHERE Orders.OrderID = @OrderID -- update the new order/changed order with the new activityid
END TRANSACTION

This is the idea of this SP. I suggest you to also read this blog post by Alex Kuznetsov
Patterns that don't work as expected for some ideas of improving the above scheme. (Read comments to this blog).