ORA-04091: table Table_Name is mutating, trigger/function may not see it [message #670567] |
Fri, 13 July 2018 06:19 |
|
Mukul Ahmed
Messages: 16 Registered: December 2015 Location: Bangladesh
|
Junior Member |
|
|
I have written a trigger for inserting to a new table from one table.
Here is the code-
CREATE OR REPLACE TRIGGER TMS_DUE_PAY_RCV_TRIG
AFTER INSERT ON TMS_DUE_PAY_RCV
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_trans_type VARCHAR2(10);
BEGIN
select TRANS_TYPE into v_trans_type from TMS_DUE_PAY_RCV where PAYMENT_ID=:NEW.PAYMENT_ID;
if
v_trans_type = 'TP' then
INSERT INTO TMS_TRANSACTION (TRANS_DATE, TRANS_TYPE, INFO_ID, CASH_IN_INFO,CASH_OUT_INFO, CASH_IN_AMT, CASH_OUT_AMT,CREATE_BY,STATUS )
values(:NEW.PAY_DATE,'CASH OUT', :NEW.PAYMENT_ID,NULL, 'Purchase Due Payment',0, :NEW.PAID_AMT,:NEW.CREATE_BY,'A');
else
INSERT INTO TMS_TRANSACTION (TRANS_DATE, TRANS_TYPE, INFO_ID, CASH_IN_INFO, CASH_OUT_INFO, CASH_IN_AMT, CASH_OUT_AMT,CREATE_BY, STATUS )
values(:NEW.PAY_DATE,'CASH IN', :NEW.PAYMENT_ID, 'Due Received',NULL, :NEW.PAID_AMT,0, :NEW.CREATE_BY,'A');
end if;
END TMS_DUE_PAY_RCV_TRIG ;
/
But when inserting then an error is showing like-
'ORA-04091: table TMS_DUE_PAY_RCV is mutating, trigger/function may not see it ORA-06512: at "TMS_DUE_PAY_RCV_TRIG",
line 7 ORA-04088: error during execution of trigger 'TMS.TMS_DUE_PAY_RCV_TRIG
Please Help me..
-----
Lalit: Formatted code and error block
Code is different from English, hence must be treated differently. In future, please do by yourself as suggested in below message.
[Updated on: Sat, 21 July 2018 11:58] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|