0

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;```
astentx
  • 6,393
  • 2
  • 16
  • 25
skalluri
  • 53
  • 1
  • 8
  • Does this answer your question? [Oracle trigger error ORA-04091](https://stackoverflow.com/questions/50966543/oracle-trigger-error-ora-04091) – astentx Nov 12 '22 at 06:52

1 Answers1

2

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.

pmdba
  • 6,457
  • 2
  • 6
  • 16
  • :new.id = 'H'||:old.clock; this line of code is it updating only for that row? hopefully do not corrupt other rows , how does it know which row to update... (sorry I am a beginner in PL/SQL) – skalluri Nov 12 '22 at 12:26
  • It will only update the current row, because that is the scope of the trigger: `FOR EACH ROW`. – pmdba Nov 12 '22 at 14:11
  • I am getting the error Error report - ORA-04084: cannot change NEW values for this trigger type 04084. 00000 - "cannot change NEW values for this trigger type" *Cause: New trigger variables can only be changed in before row insert or update triggers. *Action: Change the trigger type or remove the variable reference. – skalluri Nov 14 '22 at 15:37
  • OK I solved the problem by changing the Trigger defenited to "BEFORE UPDATE" instead of "AFTER UPDATE" which solved the issues. – skalluri Nov 14 '22 at 15:46