Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Mutating Trigger Quandry.

Mutating Trigger Quandry.

From: Preston <dontwantany_at_nowhere.invalid>
Date: 6 Jul 2006 06:05:25 GMT
Message-ID: <4h3nh5F1otidhU1@individual.net>


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.
Received on Thu Jul 06 2006 - 01:05:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US