Re: Odd(?) RI-trigger behavior

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Didn't someone (Peter?) say that the mandated firing order was based on
> creation order/time in SQL99?

It does say that:
        The order of execution of a set of triggers is ascending by value        of their timestamp of creation in
theirdescriptors, such that the        oldest trigger executes first. If one or more triggers have the        same
timestampvalue, then their relative order of execution is        implementation-defined.
 

However, this strikes me as fairly brain-dead; it's unnecessarily hard
to control the order of trigger execution.  You have to drop and
recreate triggers if you want to insert a new one at a desired position.
Worse, if you create several triggers in the same transaction, they'll
have the same timestamp --- leaving you right back in the
implementation-defined case.  But if you want to make your rearrangement
atomically with respect to other transactions, you have little choice
but to drop/recreate in one xact.  Looks like a catch-22 to me.

ISTM we had discussed this before and concluded that name order was
a more reasonable definition.  Nobody had got round to doing anything
about it though.  (Indeed my current hack was not intended to provide
a predictable firing order, it just fell out that way...)
        regards, tom lane