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

Question

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

Stop and just **don't**. Data should be kept redundancy free to avoid the possibilities of inconsistencies. So writing a sum over one table into another isn't a good thing to do. If you need the sum, you can always query it and get the **right** result. For convenience you can create a view that stores such a query so you don't have to repeat it every time. — sticky bit, Jan 10 '22 at 11:04
And this `Select Transaction_Type INTO T_Type FROM TRANSACTION;` should just probably just be `t_type := :new.transaction_type;`. If `Select Transaction_Type INTO T_Type FROM TRANSACTION;` didn't throw the mutating error, it'd throw en error that there are too much values as you try squeezing the whole set of types, which will have more than one record in general, into one scalar variable. — sticky bit, Jan 10 '22 at 11:09
You're welcome. But take my first comment seriously. You're on the wrong path and staying on it will eventually cause you a lot of headaches! — sticky bit, Jan 10 '22 at 11:16
Most definitely, this won't survive a multiple, concurrent user environment. If anything, consider a materialised view. — Scott, Jan 11 '22 at 00:14

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

0 Answers0