Re: 10.1 / 10.2: different trigger behaviour

Tarby777 wrote:
> 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

Posting the actual error message and possibly the trigger code would make it possible for someone to help you.

My guess is that the two schemas are not, in fact, identical.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org