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

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?

2 Answers

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.

While I know that the reason for this question (your university assignment) is not relevant anymore. Someone else might still have some similar problem.

• If you need consistency checks in triggers do them BEFORE the update, not afterwards.
• Only check the rows actually modified by your statement.
• For this kind of operation, use a ROW trigger, not a STATEMENT trigger
• Make use of the ":OLD" and ":NEW" datasets instead of querying the table

So do something like this (NOT tested in a database, only written on the fly)

CREATE OR REPLACE TRIGGER cell_t1 BEFORE UPDATE OF maxcalls ON cell FOR EACH ROW DECLARE v_active_cnt NUMBER(20); BEGIN SELECT COUNT(*) INTO v_active_cnt FROM telephone WHERE phonestate='Active' AND x<:NEW.x1 AND x>=:NEW.x0 AND y<:NEW.y1 AND y>=:NEW.y0; IF ( :NEW.maxcalls < v_active_cnt ) THEN :NEW.maxcalls := v_active_cnt END; END; 

What this does is - Query the telephone table for the number of active phones in the cell identified by the currently modified row (:NEW dataset) - Check if the modified maxcalls value is bigger than this number and if so modify the new maxcalls value accordingly

This, by the way, is exactly what these kind of triggers are for.