From: Richard Huxton <dev@archonet.com>
To: pgsql-sql@postgresql.org
Subject: Trigger order problems
Date: Wed, 17 Sep 2003 12:20:54 +0100
Message-ID: <200309171220.54020.dev@archonet.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com
Subject: Re: Trigger order problems
In-Reply-To: <200309171220.54020.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox