CODEWITHSUNDEEP
sqltriggersoracle11gora-06512ora-04088
user2999664
user2999664

Reputation: 15

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?

Upvotes: 1

Views: 73028

Answers (2)

Jan
Jan

Reputation: 96

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.

Upvotes: 2

Ben
Ben

Reputation: 52923

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.

Upvotes: 3

Related Questions