Home » SQL & PL/SQL » SQL & PL/SQL » URGENT Mutating Problem
URGENT Mutating Problem [message #38359] Mon, 15 April 2002 09:22 Go to next message
Enmanuel
Messages: 1
Registered: April 2002
Junior Member
I'm receiving a mutating table error when i try to insert in the table 'alarmas' can you help me please:

Error on line 0
INSERT INTO alarmas
(SELECT tdma_allip.plataforma, tdma_allip.sistema, tdma_all

ORA-04091: la tabla GMONITOR.ALARMAS está mutando, puede que el trigger/la función no puedan verla
ORA-06512: en "GMONITOR.ALA_HST_BI_TGR", línea 3
ORA-06512: en "GMONITOR.ALA_HST_BI_TGR", línea 9
ORA-04088: error durante la ejecución del trigger 'GMONITOR.ALA_HST_BI_TGR'

the triggers and table samples is here:

CREATE TABLE ALARMAS (
PLATAFORMA VARCHAR2 (10) NOT NULL,
SISTEMA VARCHAR2 (10) NOT NULL,
NODO VARCHAR2 (10),
NUMERO NUMBER (4),
ID VARCHAR2 (25) NOT NULL,
ACTIVA NUMBER (1) NOT NULL,
PRIORIDAD VARCHAR2 (5),
TIPO VARCHAR2 (10),
ALARMA VARCHAR2 (250),
DATOS VARCHAR2 (300),
INICIO DATE,
FIN DATE,
ATENDIDA VARCHAR2 (25),
INICIOAT DATE)
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10518528
NEXT 10485760
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 249
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

CREATE TABLE TDMA_ALLIP (
PLATAFORMA VARCHAR2 (10) NOT NULL,
SISTEMA VARCHAR2 (10) NOT NULL,
NODO VARCHAR2 (10),
NUMERO NUMBER (4),
ID VARCHAR2 (25) NOT NULL,
ACTIVA NUMBER (1) NOT NULL,
PRIORIDAD VARCHAR2 (5),
TIPO VARCHAR2 (10),
ALARMA VARCHAR2 (250),
DATOS VARCHAR2 (300),
INICIO DATE,
FIN DATE,
ATENDIDA VARCHAR2 (25),
INICIOAT DATE)
TABLESPACE USERS
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10518528
NEXT 10485760
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 249
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

CREATE OR REPLACE PACKAGE sessval
IS
toma varchar2(100);
PROCEDURE set_mydate (date_in IN DATE);
FUNCTION mydate RETURN DATE;
END sessval;
/
CREATE OR REPLACE PACKAGE BODY sessval
IS
g_mydate DATE;

PROCEDURE set_mydate (date_in IN DATE)
IS
BEGIN
g_mydate := date_in;
END;

FUNCTION mydate RETURN DATE
IS
BEGIN
RETURN g_mydate;
END;
END sessval;
/

CREATE OR REPLACE TRIGGER ala_hst_BI_tgr
BEFORE INSERT ON alarmas FOR EACH ROW
DECLARE
CURSOR c1 IS
SELECT *
FROM alarmas
WHERE (alarmas.id = :NEW.id);
rec c1%ROWTYPE;
BEGIN
sessval.toma:='';
OPEN c1;
FETCH c1 INTO rec;
IF (c1%FOUND) THEN
sessval.toma:= rec.id;
INSERT INTO historico
Values (rec.plataforma, rec.sistema, rec.nodo, rec.numero, rec.id, :new.activa, rec.prioridad, rec.tipo, rec.alarma, rec.datos, rec.inicio, :new.fin, rec.atendida, rec.inicioat);
DELETE FROM ALARMAS WHERE alarmas.id = rec.id;
ELSIF ((c1%NOTFOUND) AND (:new.activa = 0)) THEN
sessval.toma:= :NEW.id;
END IF;
CLOSE c1;
END;
/

CREATE OR REPLACE TRIGGER ala_hst_AI_tgr
AFTER INSERT ON alarmas
BEGIN
if sessval.toma<> '' then
DELETE FROM alarmas WHERE alarmas.id = sessval.toma;
end if;
END ala_hst_AI_tgr;
/

and the probem occurs when i try to do this:

INSERT INTO alarmas
(SELECT tdma_allip.plataforma, tdma_allip.sistema, tdma_allip.nodo, tdma_allip.numero, tdma_allip.id, tdma_allip.activa, tdma_allip.prioridad, tdma_allip.tipo, tdma_allip.alarma, tdma_allip.datos, tdma_allip.inicio, tdma_allip.fin, tdma_allip.atendida, tdma_allip.inicioat
FROM tdma_allip, alarmas
WHERE ((tdma_allip.id = alarmas.id(+)) AND ((alarmas.id) IS NULL)) AND ((TDMA_ALLIP.PLATAFORMA) = 'TDMA') AND ((TDMA_ALLIP.SISTEMA)='701SCR'));
Re: URGENT Mutating Problem [message #38373 is a reply to message #38359] Mon, 15 April 2002 17:41 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
In your ala_hst_BI_tgr trigger, you cannot delete from the alarmas table without causing a mutation error. Also, you don't need to select from alarmas - just use the :new values to insert into your history table.

But you cannot delete the row you just inserted in a row trigger - this would need to be done in an after statement trigger. In the row trigger, store the id in a pl/sql table in a package, and then in an after statement trigger, loop through the pl/sql table and do your deletes. You will need to initialize this pl/sql table in a before statement trigger.
Previous Topic: dll
Next Topic: SQL tuning
Goto Forum:
  


Current Time: Fri Mar 29 03:38:04 CDT 2024