I've encountered a tricky problem that I can't see a solution to at
present. Let me describe.
I have 3 tables:
MAIN - the main data table with results in.
GROUPING - a table that is related to MAIN, with MAIM having a FK
constraint to GROUPING
AGGREGATES - a table that contains aggregated information from MAIN, in
part grouped by the info in GROUPING.
I'm filling the data in AGGREGATES using triggers on MAIN that firstly
delete the old aggregate value and then inserts a new aggregate (e.g. 2
after insert/update/delete "for each statement" triggers).
Mostly its working. When I delete a row in MAIN the row in AGGREGATES
gets deleted and then inserted again with the new aggregate.
But when I delete a row from GROUPING and the cascade delete causes the
corresponding rows in MAIN to be deleted it does not work.
I'm pretty sure this is because part of the selection criteria for the
rows to delete involves a join to GROUPING, and the rows to join to have
just been blown away by the delete operation, so nothing in AGGREGATES
gets deleted.
Is there solution to this?
Tim
