Re: Odd(?) RI-trigger behavior

On Thu, 18 Apr 2002, Tom Lane wrote:

> 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 their descriptors, such that the
>          oldest trigger executes first. If one or more triggers have the
>          same timestamp value, 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...)

I agree that name is better, I wasn't sure if we'd reached a consensus on
it or if the conversation drifted away due to the fact that noone was
looking at it at the time.