Error during trigger execution - ORA-06512 - ORA-04088

Question

I've these tables

CELL(CellId, x0, y0, x1, y1, CurrentPhone#, MaxCalls)
TELEPHONE(PhoneNo, x, y, PhoneState)
STATE_CHANGE(ChangeId, TimeStamp, PhoneNo, x, y, ChangeType)

And I've to create this trigger (the trigger is mandatory)

Changing the maximum number of active calls: The maximum number of active calls related to a single cell may be reduced by the cellular phone network for managing issues (decrease of the MaxCalls value in the CELL table). The update on the MaxCalls attribute for a single cell could cause an inconsistent situation in which the MaxCalls value in the CELL table becomes smaller than the number of currently Active phones (PhoneState=’Active’) in the considered cell. If so, the corresponding MaxCalls attribute needs to be updated with the number of currently Active phones (PhoneState=’Active’) in the considered cell

I wrote this trigger

create or replace trigger CELL_T1
AFTER UPDATE OF MAXCALLS ON CELL
BEGIN
UPDATE CELL E1
SET E1.MAXCALLS=(
    SELECT COO
    FROM (SELECT E2.CELLID, COO
          FROM CELL E2, (
             SELECT CELLID, COUNT(*) COO
             FROM CELL C2, TELEPHONE
             WHERE PhoneState='Active' AND x<x1 AND x>=x0 AND y<y1 AND y>=y0
             GROUP BY C2.CellId
             )TW
         WHERE E2.CELLID=TW.CELLID AND COO>E2.MAXCALLS
         )
 )
WHERE  E1.CELLID IN (
SELECT C1.CELLID
FROM CELL C1, (
            SELECT CELLID, COUNT(*) COO
            FROM CELL C3, TELEPHONE
            WHERE PhoneState='Active' AND x<x1 AND x>=x0 AND y<y1 AND y>=y0
            GROUP BY C3.CellId
            )TW1
WHERE C1.CELLID=TW1.CELLID AND COO>C1.MAXCALLS and e1.cellid=tw1.cellid
         );    
END;​

The trigger compiled without a problem; then I wrote the update statement:

UPDATE CELL SET MAXCALLS=MAXCALLS-2;

but I got these errors:

ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-06512: at "MATTEO.CELL_T1", line 2
ORA-04088: error during execution of trigger 'MATTEO.CELL_T1'
ORA-0

1. UPDATE CELL
2. SET MaxCalls = MaxCalls-2;

I can't find anything wrong in my trigger; what's going wrong?

Answer 1

The problem is most likely that you're doing a recursive update here. When you execute your UPDATE your trigger does another UPDATE, which fires the trigger which does another UPDATE in a never ending cycle. Until, that is, Oracle gets bored and raises all those exceptions.

In this situation there's really only two ways out.

• Don't UPDATE the table and use an INSTEAD OF DML trigger on a view (or something) so that the trigger only fires once.
• Remove all of this logic into a stored procedure and don't use a trigger at all.