update trigger to update into same table

Question

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"

Answer 1

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.

Answer 2

At first I would like to state that You should not design your logic in this way. I don't know what is above your trigger but it should be that upper layer's role to update PAID_AMOUNT and PENDING values. Putting application logic in triggers can lead only to chaos.

But. If You have no control over application above database and have to do this then You should change your trigger to be for each statement. Inside it create a loop to iterate over NEW_TABLE and update each row accordingly. But remember to put a condition on Your update like this:

UPDATE admin.patient_registration d
    set d.PAID_AMOUNT = p_PAIDAMOUNT_TEMP + p_DIFFERENCE 
    where d.PAID_AMOUNT != (p_PAIDAMOUNT_TEMP + p_DIFFERENCE) -- this is important
    and d.patient_id != p_patient_id

to prevent trigger from triggering itself.

Inside 'statement' trigger you can modify trigger's 'base' table.

Here you can read more about it.