Mutating Table

From: <sejohnson_at_pub.mcleodusa.com>
Date: 2000/06/22
Message-ID: <8isvcq$tja$1@nnrp1.deja.com>#1/1

I'm in Oracle 7.3.4 and have a (before insert/update trigger for each row) on a employee_pay_period table. In order to calculate a certain type of pay I have to compare the value inserted with an averaged value of data already in the table:

"select avg(commission_pay) from employee_pay_period where emp_id=..."

This causes a mutating table error. I changed the code to write to a PL/SQL table in the before insert/update and added an after insert/update trigger to read from the PL/SQL table and update the triggered table (employee_pay_period). This got me passed the mutating table problem but caused me to be in a loop and generated the error:

ERROR at line 1:
ORA-00036: Maximum number of recursive sql levels (50) exceeded

Is there a way that I can do what I want to do?

Any help would be appreciated.

Thanks...

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 22 2000 - 00:00:00 CDT