Re: 2 b4 update triggers, what is fire sequence?

On Wed, 12 Feb 2003 01:55:08 +0000, Michael wrote:

> and upgrades of the package will overwrite it. I know the docs say
> the trigger firing is not necessarily in a particular order, BUT unless
> you're going to tell me that they're intentionally randomizing it there
> has to be a way to figure it out.

There isn't, trust me. Not that I've seen the source code, so I can't tell you whether they *are* intentionally randomising it or not.

But think about it (and here I'm going to simplify ludicrously): when an insert happens on a table with multiple before insert triggers, Oracle basically has to select from a data dictionary table to see what triggers actually exist: call it TRIG$ for the sake of argument (which it isn't, but it will do).

Now, put yourself in the situation of selecting from *ANY* table: what order do the rows come out in. None. It's completely and utterly random, unless you throw in an ORDER BY clause. It depends on which blocks were on and off the freelist at the time of the inserts; who hit the ENTER key first, what blocks are already cached in the Buffer Cache, and in all probability on the wind speed and the size of the last prime number you thought of. There is NO order for returning rows from a heap table, unless you demand one.

Guess what? Oracle itself doesn't demand one from TRIG$ (or whatever the real equivalent happens to be called these days).

So it is random-ish. If you knew every possible variable, and could adjust for them, then you could probably predict the order of firing. But you don't, I don't, no-one does. So you can't.

It's random. As random as a random thing can be, anyway.

There's nothing to work out, nothing to test for, nothing to swing things a particular way.

I like to think of it as a butterfly's wing, and the resulting logical corruptions to be the IT equivalent of a hurricane in Sussex.

Chaotic, in other words.

Regards
HJR Received on Wed Feb 12 2003 - 02:00:17 CST