Search code examples
databaseoracle-databaseplsqltriggersora-04091

update trigger to update into same table


create or replace trigger admin.patreg_paid_amt_temp
before UPDATE OF PAIDAMOUNT_TEMP ON admin.patient_registration
REFERENCING OLD AS OLD NEW AS NEW
For EACH ROW 
BEGIN
   if :new.PAIDAMOUNT_TEMP != :old.PAIDAMOUNT_TEMP
   then
       UPDATE admin.patient_registration d
        set d.PAID_AMOUNT = :new.PAIDAMOUNT_TEMP + d.DIFFERENCE ,
        d.PENDING = ABS(:new.PAIDAMOUNT_TEMP - d.DUES) 
       where d.PATIENT_ID = :new.PATIENT_ID;
   end if;
END;

I know it is caused because trigger cannot change the content of the table it reads from.
Can anyone please help me in accomplishing this

When i write this query following error occurs

update admin.patient_registration set paidamount_temp= 1000 where patient_id=11;

An error was encountered performing the requested operation

ORA-04091:table Admin.PATIENT_REGISTRATION is mutating,trigger/function may not see it
ORA-06512:at "ADMIN.PATREG_PAID_AMT_TEMP",line 4
ORA-04088: error during execution of trigger "ADMIN.PATREG_PAID_AMT_TEMP"
04091. 000000-"table %s.%s is mutating,trigger/function may not see it"


Solution

  • We can manipulate the :NEW values in a trigger:

    if :new.PAIDAMOUNT_TEMP != :old.PAIDAMOUNT_TEMP
    then
        :new.PAID_AMOUNT := :new.PAIDAMOUNT_TEMP + :old.DIFFERENCE;
        :new.PENDING := ABS(:new.PAIDAMOUNT_TEMP - :new.DUES);
        :new.difference := :new.total_charges - :new.per_day_charges;
    end if;
    

    Now we know there is one PATIENT_REGISTRATION record per patient (i.e. PATIENT_ID is the primary key) we also know my solution wil work and you won't get a mutating table error.