Hi all,

I'm getting different results when I run the same script on the same
data in a 10g r1 and 10g r2 database. It works fine on r1 but goes
belly-up with constraint violations in r2. The script is applying a
delta schema, and moving a lot of data around. The first statement that
goes belly-up in r2 is one that inserts rows into a table that has an
insert/update trigger, and it seems that the problem is with the
trigger code.

Let's say my script is inserting rows into table t1. The trigger will
always create a matching record in table t2 if there isn't one there
already and depending on some of the column values in the new t1
record, it may also create a matching row in table t3. Table t2 has a
foreign key to t1, and t3 has a foreign key to t2. In other words, t2
is a child table of t1, and t3 is a child table of t2.

When all the rows have been inserted into t1, we finally hit a COMMIT
and the whole thing gets written to the database. In 10g r1 (and 9i,
for that matter) the commit is successful. In 10g r2, the transaction
is rolled back because of a constraint violation; Oracle tells me that
one of my t3 records is invalid because there's no matching t2 record
to satisfy the FK constraint on t3.

As I said, I'm using the exact same data both times; I load a dump file
and run the script. It works in r1, it doesn't work in r2. In both
cases, the databases are running on default values; I haven't
consciously done anything that would change transaction behaviour or
change the way the SQL is evaluated or executed.

What's going on?

TIA
Tarby