Reputation: 15
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
I can't find anything wrong in my trigger; what's going wrong?
Upvotes: 1
Views: 73028
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.
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
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.
Upvotes: 3