First, my apologies for not coming back to you sooner. I can make all kinds of excuses, but you were waiting for me and I should not have kept you waiting for so long. My bad.

Second, I think I found a way to do the calculation as requested. This is even more flexible than you described: any of the CS levels 1, 2, and 3 can be present 0, 1, or more than 1 time, and it should always return the correct results. The code did become quite complex and if you are using this for deletes (or updates or inserts) of massive amounts of rows in massive tables, then I do not know if it will perform well. (But in such a case, the cursor you use now will probably not perform well either).

CREATE TRIGGER [dbo].[RecalcOnDelete]

ON [dbo].[tM_RepRateV2]

AFTER DELETE

AS

BEGIN;

SET NOCOUNT ON;

WITH

ALL_CS_Values AS

(SELECT CAST(1 AS smallint) AS CS_ID

UNION ALL

SELECT 2

UNION ALL

SELECT 3),

ALL_PP_Values AS

(SELECT DISTINCT PP_ID

FROM dbo.tM_PolProd),

RepRate_Per_CS_PP AS

(SELECT p.PP_ID, c.CS_ID,

SUM(COALESCE(RR_Yr1Rate, 0)) AS SumYr1Rate,

SUM(COALESCE(RR_RenewRate, 0)) AS SumRnwRate

FROM ALL_CS_Values AS c

CROSS JOIN ALL_PP_Values AS p

LEFT JOIN dbo.tM_RepRateV2 AS r

ON r.PP_ID = p.PP_ID

AND r.CS_ID = c.CS_ID

GROUP BY p.PP_ID, c.CS_ID),

CommissionPerCS AS

(SELECT PP_ID,

0 AS CS_ID,

CAST(1000.0 AS decimal(9,2)) AS BaseYr1,

CAST(1000.0 AS decimal(9,2)) AS BaseRnw,

CAST(0.0 AS decimal(9,2)) AS CommYr1,

CAST(0.0 AS decimal(9,2)) AS CommRnw

FROM ALL_PP_Values

UNION ALL

SELECT r.PP_ID,

r.CS_ID,

CAST(c.BaseYr1 - c.CommYr1 AS decimal(9,2)),

CAST(c.BaseRnw - c.CommRnw AS decimal(9,2)),

CAST((c.BaseYr1 - c.CommYr1) * COALESCE(r.SumYr1Rate, 0) / 100.0 AS decimal(9,2)),

CAST((c.BaseRnw - c.CommRnw) * COALESCE(r.SumRnwRate, 0) / 100.0 AS decimal(9,2))

FROM CommissionPerCS AS c

INNER JOIN RepRate_Per_CS_PP AS r

ON r.PP_ID = c.PP_ID

AND r.CS_ID = c.CS_ID + 1),

TotCommPerPP AS

(SELECT PP_ID, SUM(CommYr1) AS TotCommYr1, SUM(CommRnw) AS TotCommRnw

FROM CommissionPerCS

GROUP BY PP_ID)

UPDATE dbo.tM_PolProd

SET PP_Yr1Allocn = t.TotCommYr1,

PP_RnwAllocn = t.TotCommRnw

FROM TotCommPerPP AS t

WHERE t.PP_ID = dbo.tM_PolProd.PP_ID

AND EXISTS

(SELECT *

FROM deleted AS d

WHERE d.PP_ID = dbo.tM_PolProd.PP_ID);

END;

GO

I also reailzed (unfortunately after doing all the work on the query above) that there is a much simpler way to test your requirement. The total will always be 1000 when these two conditions are met:

1. The sum of all combined rates for any PP_ID / CS_ID combination should never be more than 100%.

2. The sum of all combined rates for any PP-ID / CS-ID combination should always be exactly 100% for the highest CS_ID used for that PP_ID. (Based on your description that should always be CS_ID 3).