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.

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.

./heLen