From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Trigger order problems
Date: Wed, 17 Sep 2003 12:20:54 +0100
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200309171220.54020.dev@archonet.com>

TIA people

Three related tables:
  trans_core - transaction details 
    (trans_id, trans_owner, trans_date, trans_amount)
  trans_src - funding source 
    (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