Re: Order of triggers - totally lost

-----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(at)jonathangardner(dot)net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/OmU2WgwF3QvpWNwRAklXAJ4hv+2Fx5jZXG6ykpOMMNLvG655owCdFtEo
+eV+ZcrItpOerAPySiSPe2g=
=e1Ao
-----END PGP SIGNATURE-----