Hi Tim,
I still don't think I understand what's not working. Here's my crude
understanding:
1) You have 3 tables:
MAIN - This contains the information to be aggregated.
GROUPING - This contains facts needed to group the information in MAIN
AGGREGATES - This contains a pre-computed, grouped aggregation of MAIN,
using the groups in GROUPING
2) A foreign key from MAIN to GROUPING defines the groups. It's unclear
whether a similar foreign key links AGGREGATES to GROUPING.
3) When you delete a group from GROUPING, cascade actions delete the
rows in the group from MAIN. And some triggers bring AGGREGATES back
into agreement with the facts in MAIN and GROUPING.
I understand why deleting a group means that you need to delete the
corresponding aggregated result for that group from AGGREGATES. What I
don't understand is why you need to do anything else to AGGREGATES. Is
what's in AGGREGATES not simply the result of a grouped aggregation?
I don't think I can speculate further without knowing more about how the
contents of AGGREGATES are calculated. I understand that this may be the
special sauce of your application which you don't want to share. Maybe
you can share a simplified problem which shows why this aggregation is
so tricky.
Thanks,
-Rick
