> At 04:09 PM 30/03/2006, you wrote:
> >I have tables with foreign keys into other tables.
> >In the context of one transaction:
> >1. insert into master
> > after insert trigger to insert into a log table
> >2. insert into child with PK of master
> > after insert trigger to insert into a log table
> >commit transaction
> >
> >now - up til now, the sequence of insertion into the log table
> has reflected
> >the above sequence. If I order by the log table PK I see this
> same sequence.
> >
> >but recently I see that it is possible to get the insertion into the log
> >table out of this sequence so the log table records an insertion to the
> >child sometimes before it records an insertion into the parent.
> This reverse
> >sequence is obviously not possible into the child first since it would
> >(does) raise an FK exception.
> >
> >Q: am I crazy - seeing things?, OR is there never any guarantee of this
> >seqence within the context of the same transaction and I've been
> lucky up to
> >now, OR is there a way of ensuring this sequence somehow that
> I'm not aware
> >of?
>
> None of the above. The order in which a vanilla SELECT outputs rows
> is quite arbitrary. If there is any "natural order" at all then it
> would be by the db_key but there is nothing in the design that
> guarantees that, either. To some extent it is reasonable to suppose
> that the row with the lower generator number was written first, but
> you shouldn't make any logical assumptions about it. The two records
> might well be stored on different pages, with the higher-numbered one
> being written to a lower page number.

I'm talking about vanilla inserts - no selects here at all. The row with the
lower generator is not necessarily WRITTEN first but the gen value shoul
have been acquired first. that's my point. But I have seen that within one
transaction, the after insert trigger on a child table acquires the (lower)
gen value BEFORE the after insert trigger on the master table....

>
> The only way you might possibly determine the exact write order would
> be to add a timestamp column to the log record and invoke the UDF
> GetExactTimestamp() to write it. You still might get the same exact
> timestamp for both records, though, if the time between the creation
> of the records was less than a ten-thousandth of a second.

that's my gut on timestamp fields as well
Alan

>
> ./heLen