Moonwar
Moonwar

Reputation: 43

I was trying to write a trigger but it gives Error like this in PL/SQL

My code is:

CREATE TABLE ACCOUNT(Account_no Varchar2(5), Account_Name  Varchar2(5), Account_Balance number);
INSERT INTO ACCOUNT VALUES('A1','C1',5000);
INSERT INTO ACCOUNT VALUES('A2','C2',15000);
CREATE TABLE TRANSACTION(Transaction_no number, Src_Acc_no varchar2(5), Dst_Acc_No varchar2(5), Transaction_Type varchar2(5), Amount number);

CREATE OR REPLACE TRIGGER TRIG 
AFTER INSERT
ON TRANSACTION
FOR EACH ROW 
DECLARE
AB_1 number;
AB_2 number;
T_Type varchar2(10);
BEGIN
SELECT Account_Balance INTO AB_1 FROM ACCOUNT WHERE Account_no = :NEW.Src_Acc_No;
SELECT Account_Balance INTO AB_2 FROM ACCOUNT WHERE Account_no = :NEW.Dst_Acc_No;
Select Transaction_Type INTO T_Type FROM TRANSACTION;
IF T_Type='W' THEN 
    AB_1:=AB_1-:New.Amount;
    UPDATE ACCOUNT SET Account_Balance=AB_1 WHERE Account_no=:NEW.Src_Acc_No;

ELSIF T_Type='D' THEN 
    AB_1:=AB_1+:New.Amount;
    UPDATE ACCOUNT SET Account_Balance=AB_1 WHERE Account_no=:NEW.Src_Acc_No;

ELSIF T_Type='T' THEN
     AB_1:=AB_1-:New.Amount;
     AB_2:=AB_2+:New.Amount;
     UPDATE ACCOUNT SET Account_Balance=AB_1 WHERE Account_no=:NEW.Src_Acc_No;
     UPDATE ACCOUNT SET Account_Balance=AB_2 WHERE Account_no=:NEW.Dst_Acc_No;
END IF; 

EXCEPTION
WHEN NO_DATA_FOUND THEN 
dbms_output.put_line('no data found');
END;
/
INSERT INTO TRANSACTION VALUES(1,'A1',NULL,'W',1000);
INSERT INTO TRANSACTION VALUES(2,'A2',NULL,'D',3000);
INSERT INTO TRANSACTION VALUES(3,'A2','A1','T',5000);

but it is not giving proper output.

ERROR:

ORA-04091: table SQL_JUVARSIUJECZFPSBAIHPOCYEW.TRANSACTION is mutating, trigger/function may not see it
ORA-06512: at "SQL_JUVARSIUJECZFPSBAIHPOCYEW.TRIG_9_2017_2_60_107", line 8
ORA-06512: at "SYS.DBMS_SQL

Upvotes: 1

Views: 71

Answers (0)

Related Questions