ORA-04091 table * is mutating, trigger/function may not see it

Question

I am working on a project that rents boats to customers and based on information they enter supplies them with a boat. One section of this project I want to create a trigger that based on when you leave with the boat vs when you return it will calculate the days and then either refund money to the customer balance or charge extra. I have my trigger setup like so -

CREATE OR REPLACE TRIGGER BALANCE_FEE
AFTER INSERT ON CHARTERS
FOR EACH ROW 
DECLARE
FEE NUMBER;
ACL_DATE DATE;
EXP_DATE DATE;
GRP_ID NUMBER;

BEGIN

SELECT ACL_RETURN_DATE, EXP_RETURN_DATE, GRP_ID INTO ACL_DATE, EXP_DATE, GRP_ID FROM CHARTERS;


IF ACL_DATE > EXP_DATE
THEN
FEE := (ACL_DATE - EXP_DATE) * 75;

IF ACL_DATE < EXP_DATE
THEN
FEE := (ACL_DATE - EXP_DATE)* -20;

ELSE
FEE := 0;
END IF;
END IF;
UPDATE CUSTOMER
     SET CUSTOMER.BALANCE = CUSTOMER.BALANCE + FEE
     WHERE CUSTOMER.GRP_ID = GRP_ID;
END;
/
SHOW ERROR;

If it also helps here is the two tables that I have that relate to this trigger.

CREATE TABLE CUSTOMER (
    CUS_FNAME VARCHAR(20),
    CUS_LNAME VARCHAR(20),
    GENDER VARCHAR(20),
    PHONENUM NUMBER(10),
    CITY VARCHAR(20),
    PARTY_COUNT INT,
    GRP_ID VARCHAR(20) PRIMARY KEY,
    BALANCE NUMBER);

CREATE TABLE CHARTERS (
    CHARTER_ID VARCHAR(20),
    BOAT_ID VARCHAR(20) REFERENCES BOAT(BOAT_ID),
    GRP_ID VARCHAR(20) REFERENCES CUSTOMER(GRP_ID),
    EXP_RETURN_DATE DATE,
    ACL_RETURN_DATE DATE);

When I run the code I get this error report

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T003','B003',DATE '2019-05-5',DATE '2019-05-07','G003')
Error report -
ORA-04091: table ADMIN_BF.CHARTERS is mutating, trigger/function may not see it
ORA-06512: at "ADMIN_BF.BALANCE_FEE", line 9
ORA-04088: error during execution of trigger 'ADMIN_BF.BALANCE_FEE'

What exactly is the problem here? Is it something to do with my trigger being an "After Insert"? Thanks in advance.

Answer 1

You can't select from a table which is being updated right now, as it is "mutating". Instead of the SELECT statement, do this:

acl_date := :new.acl_return_date;
exp_date := :new.exp_return_date;
grp_id   := :new.grp_id;

The rest of code should be OK afterwards.

Answer 2

CREATE OR REPLACE TRIGGER trg_Facturation1_UID AFTER INSERT OR UPDATE OR DELETE ON Facturation1 FOR EACH ROW DECLARE v_count NUMBER; BEGIN IF INSERTING THEN SELECT COUNT(*) INTO v_count FROM Facturation WHERE FacturationId = :NEW.FacturationId; IF v_count = 0 THEN INSERT INTO Facturation(FacturationId, TransportId, DateFacture, MontantFacture, MontantRglt, DateRglt, ModeRglt, UserDateTDWH, UserNameDWH) VALUES(:NEW.FacturationId, :NEW.TransportId, :NEW.DateFacture, :NEW.MontantFacture, :NEW.MontantRglt, :NEW.DateRglt, :NEW.ModeRglt, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),USER); END IF; END IF;

IF UPDATING THEN
    INSERT INTO Facturation(FacturationId, TransportId, DateFacture, MontantFacture, MontantRglt, DateRglt, ModeRglt,  UserDateTDWH, UserNameDWH)
    VALUES(:NEW.FacturationId, :NEW.TransportId, :NEW.DateFacture, :NEW.MontantFacture, :NEW.MontantRglt, :NEW.DateRglt, :NEW.ModeRglt, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'),USER);
END IF;

IF DELETING THEN
    INSERT INTO Facturation(FacturationId, TransportId, DateFacture, MontantFacture, MontantRglt, DateRglt, ModeRglt,  UserDateTDWH, UserNameDWH)
    VALUES(:OLD.FacturationId, :OLD.TransportId, :OLD.DateFacture, :OLD.MontantFacture, :OLD.MontantRglt, :OLD.DateRglt, :OLD.ModeRglt, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), USER);
END IF;

END; /