From: Jonathan Gardner <jgardner@jonathangardner.net>
To: "=?iso-8859-2?q?SZ=DBCS?= =?iso-8859-2?q?=20G=E1bor?="
	<surrano@mailbox.hu>, <pgsql-sql@postgresql.org>
Subject: Re: Order of triggers - totally lost
Date: Wed, 13 Aug 2003 09:20:06 -0700
User-Agent: KMail/1.5
References: <001001c36183$4f636570$0403a8c0@fejleszt4>
	<200308130754.32211.jgardner@jonathangardner.net>
	<003e01c361b1$37d02780$0403a8c0@fejleszt4>
In-Reply-To: <003e01c361b1$37d02780$0403a8c0@fejleszt4>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200308130920.07619.jgardner@jonathangardner.net>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 13 August 2003 08:40, SZ=DBCS G=E1bor 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=20
doing and why I should use them. This tends to minimize the number of=20
them hanging around. I am a programmer - python, perl mostly - so the=20
logic flow of triggers isn't something I can keep a firm handle on all=20
the time.

Here are some examples of when I use triggers:

 - One column is completely dependent on one or more other columns=20
(e.g., total_revenue, total_cost, profit). A 'before' insert/update=20
trigger works here. That one trigger can do all of the calculations for=20
the row.

 -  A row is summary of several other rows in other tables. This is=20
useful for collecting real-time stats, but is difficult to get right.=20
Here, I use 'after' triggers. I also copiously document how it works,=20
because there are always nasty bugs waiting to bite.

 - A particularly nasty constraint, that isn't as simple as "not null".=20
If you noticed, the foreign key constraints are implemented with three=20
triggers - one on the referencing table, and two on the referenced=20
table. There are some other situations where you may want constraints=20
that aren't as clear-cut as a foreign key that will require multiple=20
'before' triggers on multiple tables.

When I handle a complicated procedure that involves inserting multiple=20
rows into multiple tables, I tend to put those into plpgsql procedures.=20
That way, I can keep control of everything and keep it clean. For=20
instance, placing an order with several items.

My tables only end up with a couple of triggers, if any. I have a ton of=20
stored procedures lying around, however -- pretty much one for each=20
"action" a user would take to modify the database. If a trigger=20
triggers another trigger, they aren't dependant on the order thereof,=20
or even the existance of the other trigger.

You may also want to examine PostgreSQL's RULE system (CREATE RULE). I=20
think some of your triggers may be interchangeable with rules.

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/OmU2WgwF3QvpWNwRAklXAJ4hv+2Fx5jZXG6ykpOMMNLvG655owCdFtEo
+eV+ZcrItpOerAPySiSPe2g=3D
=3De1Ao
-----END PGP SIGNATURE-----