Re: Mutating Trigger Quandry.

Preston wrote:
> 10gR2 on WinX
>
> This one has me stumped. We've got a table that holds stock deal
> transactions - purchases, sales, corporate events etc. This table has a
> 'before insert' trigger that does various things, one of which is to
> update the 'cost of sale' for any sales that already exists in the
> table with a later transaction date than the new record being inserted.
> This involves selecting some of the existing transactions from the
> table that's being inserted into, which is the part causing the error.
>
> The transactions are created through the GUI app, which is written in
> USoft. It basically just uses SQL, so there's a button that does
> 'INSERT INTO etc...' when clicked. This process has been working fine
> for years.
>
> However I've written a package to handle the inserts for a new
> transaction type, as I want it to be accessible from other GUI apps.
> This package also does an 'INSERT INTO etc...', but generates a
> mutating trigger error. I've tried just doing a simple 'INSERT...
> VALUES...' in SQL Plus, & that too gives the error. However if I do the
> exact same statement from within the ad-hoc SQL tool in USoft, it works
> fine - no mutating trigger error, & the trigger does what it's supposed
> to do.
>
> So I'm now confused. Why would the trigger behave itself when an
> 'INSERT... VALUES...' is done in USoft, but not when it's done in SQL
> Plus, PL/SQL Developer, or a package? And is there any way this will
> ever work, bearing in mind it has to be a 'BEFORE INSERT' trigger, &
> has to update existing records in the table when a new record is
> inserted?
>
>
> --
> Cheers, Preston.

Use the generic solution on http://asktom.oracle.com search for 'Mutating Tables'

Other than that, enable tracing prior to executing the insert.

-- 
Sybrand Bakker
Senior Oracle DBA