From: Jonathan Gardner <jgardner@jonathangardner.net>
To: SZŰCS Gábor <surrano@mailbox.hu>
To: pgsql-sql@postgresql.org
Subject: Re: Order of triggers - totally lost
Date: Wed, 13 Aug 2003 09:20:06 -0700
Message-ID: <200308130920.07619.jgardner@jonathangardner.net> (raw)
In-Reply-To: <003e01c361b1$37d02780$0403a8c0@fejleszt4>
References: <001001c36183$4f636570$0403a8c0@fejleszt4>
<200308130754.32211.jgardner@jonathangardner.net>
<003e01c361b1$37d02780$0403a8c0@fejleszt4>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 13 August 2003 08:40, SZŰCS Gábor wrote:
> Actually, the situation I painted is much much simplified compared to
> the real one (about 20 or more tables are accessed during that
> "simple 1-line update"). What I'd probably use best, are some generic
> guidelines:
>
I try to avoid triggers unless it is completely obvious what they are
doing and why I should use them. This tends to minimize the number of
them hanging around. I am a programmer - python, perl mostly - so the
logic flow of triggers isn't something I can keep a firm handle on all
the time.
Here are some examples of when I use triggers:
- One column is completely dependent on one or more other columns
(e.g., total_revenue, total_cost, profit). A 'before' insert/update
trigger works here. That one trigger can do all of the calculations for
the row.
- A row is summary of several other rows in other tables. This is
useful for collecting real-time stats, but is difficult to get right.
Here, I use 'after' triggers. I also copiously document how it works,
because there are always nasty bugs waiting to bite.
- A particularly nasty constraint, that isn't as simple as "not null".
If you noticed, the foreign key constraints are implemented with three
triggers - one on the referencing table, and two on the referenced
table. There are some other situations where you may want constraints
that aren't as clear-cut as a foreign key that will require multiple
'before' triggers on multiple tables.
When I handle a complicated procedure that involves inserting multiple
rows into multiple tables, I tend to put those into plpgsql procedures.
That way, I can keep control of everything and keep it clean. For
instance, placing an order with several items.
My tables only end up with a couple of triggers, if any. I have a ton of
stored procedures lying around, however -- pretty much one for each
"action" a user would take to modify the database. If a trigger
triggers another trigger, they aren't dependant on the order thereof,
or even the existance of the other trigger.
You may also want to examine PostgreSQL's RULE system (CREATE RULE). I
think some of your triggers may be interchangeable with rules.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/OmU2WgwF3QvpWNwRAklXAJ4hv+2Fx5jZXG6ykpOMMNLvG655owCdFtEo
+eV+ZcrItpOerAPySiSPe2g=
=e1Ao
-----END PGP SIGNATURE-----
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: jgardner@jonathangardner.net, surrano@mailbox.hu
Subject: Re: Order of triggers - totally lost
In-Reply-To: <200308130920.07619.jgardner@jonathangardner.net>
* 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