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"
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.