Sunday, November 12, 2006
Solving definetively the mutation error on triggers ORA-04091: table X is mutating
I had several times this error, to get the creation of a historic table of modifications.
This problem is simply solved adding three lines
PRAGMA AUTONOMOUS_TRANSACTION; <-- You have to add this 1/3
COMMIT; <-- You have to add this 2/3
ROLLBACK; <-- You have to add this 3/3
For example:
CREATE OR REPLACE TRIGGER OWNER.TGR_XXX
AFTER
DELETE OR UPDATE
ON OWNER.TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
cTipo VARCHAR2( 100 );
cHI_USUARIOSIS VARCHAR2( 1500 );
cHI_MAQUINA VARCHAR2( 1500 );
dHI_FECHASIS DATE;
cHI_FECHALOGON VARCHAR2( 1500 );
cHI_PROGRAMA VARCHAR2( 1500 );
PRAGMA AUTONOMOUS_TRANSACTION; <-- You have to add this 1/3
BEGIN
SELECT LOGON_TIME, OSUSER, MACHINE, PROGRAM
INTO cHI_FECHALOGON, cHI_USUARIOSIS , cHI_MAQUINA, cHI_PROGRAMA
FROM V$SESSION WHERE AUDSID = USERENV( 'SESSIONID' ) AND ROWNUM=1;
IF UPDATING THEN
cTipo := 'U';
ELSIF DELETING THEN
cTipo := 'D';
END IF;
INSERT INTO OYM.DSVERSION_ME_RW
HI_USUARIORED ,
HI_USUARIOSIS ,
HI_MAQUINA ,
HI_FECHASIS ,
HI_FECHARED ,
HI_FECHALOGON ,
HI_ACCION ,
HI_PROGRAMA )
SELECT
USER,
CHI_USUARIOSIS ,
CHI_MAQUINA ,
DHI_FECHASIS ,
SYSDATE ,
CHI_FECHALOGON ,
CTIPO,
CHI_PROGRAMA
FROM OWNER.TABLE_2
WHERE FIELD = :OLD.FIELD;
COMMIT; <-- You have to add this 2/3
EXCEPTION WHEN OTHERS THEN
DB_ERR_GRABA ( 0, 'OWNER.TGR_XXX:'||sqlerrm );
ROLLBACK; <-- You have to add this 3/3
END;
/
¶ 9:08 AM
I had several times this error, to get the creation of a historic table of modifications.
This problem is simply solved adding three lines
PRAGMA AUTONOMOUS_TRANSACTION; <-- You have to add this 1/3
COMMIT; <-- You have to add this 2/3
ROLLBACK; <-- You have to add this 3/3
For example:
CREATE OR REPLACE TRIGGER OWNER.TGR_XXX
AFTER
DELETE OR UPDATE
ON OWNER.TABLE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
cTipo VARCHAR2( 100 );
cHI_USUARIOSIS VARCHAR2( 1500 );
cHI_MAQUINA VARCHAR2( 1500 );
dHI_FECHASIS DATE;
cHI_FECHALOGON VARCHAR2( 1500 );
cHI_PROGRAMA VARCHAR2( 1500 );
PRAGMA AUTONOMOUS_TRANSACTION; <-- You have to add this 1/3
BEGIN
SELECT LOGON_TIME, OSUSER, MACHINE, PROGRAM
INTO cHI_FECHALOGON, cHI_USUARIOSIS , cHI_MAQUINA, cHI_PROGRAMA
FROM V$SESSION WHERE AUDSID = USERENV( 'SESSIONID' ) AND ROWNUM=1;
IF UPDATING THEN
cTipo := 'U';
ELSIF DELETING THEN
cTipo := 'D';
END IF;
INSERT INTO OYM.DSVERSION_ME_RW
HI_USUARIORED ,
HI_USUARIOSIS ,
HI_MAQUINA ,
HI_FECHASIS ,
HI_FECHARED ,
HI_FECHALOGON ,
HI_ACCION ,
HI_PROGRAMA )
SELECT
USER,
CHI_USUARIOSIS ,
CHI_MAQUINA ,
DHI_FECHASIS ,
SYSDATE ,
CHI_FECHALOGON ,
CTIPO,
CHI_PROGRAMA
FROM OWNER.TABLE_2
WHERE FIELD = :OLD.FIELD;
COMMIT; <-- You have to add this 2/3
EXCEPTION WHEN OTHERS THEN
DB_ERR_GRABA ( 0, 'OWNER.TGR_XXX:'||sqlerrm );
ROLLBACK; <-- You have to add this 3/3
END;
/
¶ 9:08 AM
