TIA people
Three related tables: trans_core - transaction details (trans_id, trans_owner, trans_date, trans_amount) trans_src -
fundingsource (core_id, src_id, src_amount) src_summary - summary of funding-source totals (summary_date,
summary_src,summary_amount)
The total trans_amount must always equal the sum of the linked "src_amount"s
and these funding-sources are totalled to the summary table.
The problem:
I want to update the summary table whenever trans_src is modified, but to do
so I need to get information from trans_core (trans_owner, trans_date). Now
that's not a problem with UPDATE or INSERT since the corresponding trans_core
must exist. The problem is with DELETE.
If I have the following sequence:
1. DELETE row from trans_core
2. Cascades to several DELETEs on trans_src
3. BEFORE DELETE trigger is called for each of these
4. Summary table is decremented using details from trans_src and trans_core
Of course, the problem is that by step 4 there isn't a row in trans_core to
refer to...
Options I have considered:
1. Duplicate required fields from trans_core in trans_src (yuck - in the real
tables there are several fields I'd need)
2. Wipe and recalculate all relevant summary info every time trans_core is
modified and make sure that we touch trans_core every time trans_src gets
updated.
3. Replace src_summary with a view. Can't do this, since some of the
information is historical for which we don't have any transactions to back up
the summary.
I'm going with #2 at the moment, but it seems wasteful to recalculate more
than I need to. Anyone got any smart ideas?
-- Richard Huxton Archonet Ltd