CODEWITHSUNDEEP
oracle-databasetriggersora-04091
skalluri
skalluri

Reputation: 53

Oracle Trigger error - ORA-04091: table is mutating, trigger/function may not see it

I need to write a trigger, if a particular field value is changed (say employee type from salary to hourly or hourly to salary) then the the record id value need to be updated.

Note: for me old_clock will maintain the id without prefix S or H

example of id: S222222 or H3333333

clock: 222222 or 333333

I am getting the error

One error saving changes to table "USERS":

Row 1: ORA-04091: table USERS is mutating, trigger/function may not see it

ORA-06512: at "TRIGGER_UPDATE_USERS_ID", line 20

ORA-04088: error during execution of trigger 'TRIGGER_UPDATE_USERS_ID'

CODE:

```create or replace TRIGGER "TRIGGER_UPDATE_USERS_ID" 

AFTER UPDATE OF

empl_type

ON USERS

FOR EACH ROW

DECLARE



new_empl_type varchar(5);

old_empl_type varchar(5);

old_pw_clock varchar(10);







BEGIN

new_empl_type := :NEW.empl_type;

old_empl_type := :OLD.empl_type;

old_clock := :OLD.clock;





if 

old_empl_type ='S' and new_empl_type ='H' 

then

 UPDATE USERS set id = 'H'||old_clock 

 where id = 'S222222';

 DBMS_OUTPUT.PUT_LINE('updated with new id prefix to Z '|| new_empl_type);

elsif 

old_empl_type ='H' and new_empl_type ='S' 

then

 UPDATE USERS set id = 'S'||old_clock

 where id = 'H274952';

 DBMS_OUTPUT.PUT_LINE('updated with new id prefix to M '|| new_empl_type);

end if;

END;```

Upvotes: 0

Views: 485

Answers (1)

pmdba
pmdba

Reputation: 7033

You can't modify - or even reference - the table to which the trigger is attached. You can only modify the fields in the row being updated, something like this:

create or replace TRIGGER TRIGGER_UPDATE_USERS_ID
BEFORE UPDATE OF
empl_type
ON USERS
FOR EACH ROW
BEGIN
if :old.empl_type ='S' and :new.empl_type ='H' 
then
   :new.id = 'H'||:old.clock;
   DBMS_OUTPUT.PUT_LINE('updated with new id prefix to Z '|| :new.empl_type);
elsif old.empl_type ='H' and :new.empl_type ='S' 
then
    :new.id = 'S'||:old.clock
    DBMS_OUTPUT.PUT_LINE('updated with new id prefix to M '|| :new.empl_type);
end if;
END;
/

While you could technically create an autonomous transaction within the trigger, it is inadvisable in this case because it would certainly lead to corrupted data if the original transaction on USERS failed.

Upvotes: 2

Related Questions