CODEWITHSUNDEEP
oracle-databasetriggers
Boooo402
Boooo402

Reputation: 27

ORA-04091: table * is mutating, trigger/function may not see it. Updating same table

I have a trigger that checks to see if a customer has enough of a balance to rent something. If it too high of a debt (balance) it will give them Can_Rent = 'No'.

CREATE OR REPLACE TRIGGER BALANCE_CHECK
AFTER INSERT OR UPDATE OR DELETE ON CUSTOMER
FOR EACH ROW
DECLARE
BAL NUMBER;
GRP_ID VARCHAR(20);
RENT VARCHAR(20);

BEGIN
RENT := :NEW.CAN_RENT;
GRP_ID := :NEW.GRP_ID;
BAL := :NEW.BALANCE;

IF BAL >= 400
THEN
UPDATE CUSTOMER
SET CUSTOMER.CAN_RENT = 'NO'
WHERE GRP_ID =  :NEW.GRP_ID;

ELSIF BAL < 400
THEN
UPDATE CUSTOMER
SET CUSTOMER.CAN_RENT = 'YES'
WHERE GRP_ID =  :NEW.GRP_ID;

END IF;

END;
/

When ran I get this error

INSERT INTO CUSTOMER (CUS_FNAME, CUS_LNAME, GENDER, PHONENUM, CITY, PARTY_COUNT, GRP_ID, BALANCE, CAN_RENT) VALUES ('JOHN','MADDEN','MALE','4441231234','HOUSTON',25,'G004',3000,'NO')
Error report -
ORA-04091: table ADMIN_BF.CUSTOMER is mutating, trigger/function may not see it
ORA-06512: at "ADMIN_BF.BALANCE_CHECK", line 13
ORA-04088: error during execution of trigger 'ADMIN_BF.BALANCE_CHECK'

Here is the code for the table "Customer"

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

What am I doing that is causing this error to show? Is it something to do with my "Can_Rent" variable?

Upvotes: 1

Views: 1299

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

Don't UPDATE a table you're currently modifying; it causes the mutating table error.

Trigger should be like this:

CREATE OR REPLACE TRIGGER BALANCE_CHECK
   BEFORE INSERT OR UPDATE OR DELETE
   ON CUSTOMER
   FOR EACH ROW
DECLARE
   BAL     NUMBER;
   GRP_ID  VARCHAR (20);
   RENT    VARCHAR (20);
BEGIN
   RENT := :NEW.CAN_RENT;
   GRP_ID := :NEW.GRP_ID;
   BAL := :NEW.BALANCE;

   IF BAL >= 400
   THEN
      :new.can_rent := 'NO';              --> this
   ELSIF BAL < 400
   THEN
      :new.can_rent := 'YES';             --> this
   END IF;
END;
/

Upvotes: 1

Related Questions