Error Position: 0 Return: 4091 - ORA-04091: mutating, trigger/function

Toad for Oracle & DevOps Toolkit
1

Hi ,

I have created a trigger which i got help from this forum , but i get the below error.

Should i create any compount trigger to update my table.

Here is my trigger

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2
AFTER UPDATE OF lastsignondttm
ON SYSADM.PSOPRDEFN
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,
fzap_reason_cd,dbname,lastupdoprid,lastupddttm)

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,’ ‘,’ ',:new.lastupddttm,:new.lastupdoprid
from sysadm.psroleuser b
where b.roleuser = :new.oprid
and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);
END;

Error Position: 0 Return: 4091 - ORA-04091: table SYSADM.PSOPRDEFN is mutating, trigger/function may not see it ORA-06512: at “SYSADM.Z_LOGIN3”, line 2 ORA-04088: error during execution of trigger ‘SYSADM.Z_LOGIN3’
Failed SQL stmt:UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,‘YYYY-MM-DD-HH24.MI.SS.FF’) WHERE OPRID = :2

2

Hey Winona,

You’d probably have better luck on the Oracle forum instead of the Toad forum, but my knee jerk here is that the ORA-4091 is on trigger Z_LOGIN3, where you have listed Z_LOGIN2. But yes, an additional statement-level trigger would likely be your answer, easily googleable.

GL!

Rich

3

Hi Rich,

Thanks for your suggestion. I changed my query like below and got rid off mutating error.

owever i am getting a new error

ORA-01843: not a valid month"

here is my below trigger

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2

AFTER UPDATE OF lastsignondttm

ON SYSADM.PSOPRDEFN

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

DECLARE

Mutating EXCEPTION;

PRAGMA EXCEPTION_INIT (Mutating, -4091);

errorm VARCHAR2 (2000);

BEGIN

INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,

fzap_reason_cd,dbname,lastupdoprid,lastupddttm)

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,’ ‘,’ ',:new.lastupddttm,:new.lastupdoprid

from sysadm.psroleuser b

where b.roleuser = :new.oprid

and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);

EXCEPTION

WHEN Mutating

THEN

errorm := SQLERRM;

– DBMS_OUTPUT.PUT_LINE(‘prompt 13’);

–INSERT_LOG('Error at Level ‘||LOG_LEVEL||’ with error message '||errorm);

WHEN NO_DATA_FOUND

THEN

NULL;

– DBMS_OUTPUT.PUT_LINE(‘prompt 14’);

END;

4

how does "SYSADM.PS_FZAP_AUDIT_SOX " table description looks like?