10.1 / 10.2: different trigger behaviour

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 Received on Fri Jan 19 2007 - 10:58:55 CST