Trigger causes an error because it is mutating the table

I'm writing a trigger that is supposed to add an ID of a user that has multiple bank accounts to another table once the sum of all balances he has goes over 10000 dollars. the accounts can be in different currencies so they also have to be calculated in dollars. here is the trigger:

`CREATE OR REPLACE TRIGGER ADD_NEW_VIP
AFTER UPDATE OF BALANCE ON BANK_ACCOUNT
for EACH row
DECLARE
    v_sum number :=0;
BEGIN
    SELECT SUM(ba.BALANCE *c.COURSE_TO_LEV)
    INTO v_sum 
    FROM bank_account ba
    INNER JOIN currency c ON ba.CURRENCY_ID=c.currency_id
    where ba.CUSTOMER_ID =  :NEW.CUSTOMER_ID;
    IF v_sum > 100000 THEN
        INSERT INTO VIP_CUSTOMER (CUSTOMER_ID)
        VALUES (:NEW.CUSTOMER_ID);
    END IF;
END;`

it trows SQL Error: ORA-04091: table STU2001321050_PROJECT.BANK_ACCOUNT is mutating, trigger/function may not see it ORA-06512: at "STU2001321050_PROJECT.ADD_NEW_VIP", line 4 ORA-04088: error during execution of trigger 'STU2001321050_PROJECT.ADD_NEW_VIP' 04091. 00000 - "table %s.%s is mutating, trigger/function may not see it" *Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. *Action: Rewrite the trigger (or function) so it does not read that table.

I have tried changing up the select statement but the error still appears when I try to perform only the select for a particular id it returns the correct sum and it returns correct. I suspect that the error comes from this line SELECT SUM(ba.BALANCE *c.COURSE_TO_LEV) but i need to calculate the sum after its been updated.

Solution

Use an AFTER STATEMENT trigger to only process the new balances after all modifications have occurred. If you want to only modify the changed customers then use a compound trigger.

Create a collection type to store the changed ids:

CREATE TYPE id_list
  AS TABLE OF NUMBER(10,0) -- Match data type of BANK_ACCOUNT.CUSTOMER_ID

Then the trigger:

CREATE TRIGGER add_new_vip
FOR UPDATE OF BALANCE ON BANK_ACCOUNT
COMPOUND TRIGGER
  ids id_list := id_list();
AFTER EACH ROW
  IS
  BEGIN
    ids.EXTEND;
    ids(ids.COUNT) := :NEW.customer_id;
  END AFTER EACH ROW;  
AFTER STATEMENT
  IS
  BEGIN
    MERGE INTO vip_customer dst
    USING (
      SELECT a.customer_id
      FROM   bank_account a
             INNER JOIN currency c
             ON a.currency_id = c.currency_id
      WHERE  a.customer_id IN (SELECT COLUMN_VALUE FROM TABLE(ids))
      GROUP BY a.customer_id
      HAVING SUM(a.balance *c.course_to_lev) > 100000
    ) src
    ON (dst.customer_id = src.customer_id)
    WHEN NOT MATCHED THEN
      INSERT (CUSTOMER_ID) VALUES (src.customer_id);
  END AFTER STATEMENT;
END;
/

Which, for the sample data:

CREATE TABLE bank_account (account_id, customer_id, currency_id, balance) AS
SELECT 1, 1, 1, 100000 FROM DUAL UNION ALL
SELECT 2, 1, 2, 100000 FROM DUAL UNION ALL
SELECT 3, 1, 3, 200000 FROM DUAL;

CREATE TABLE currency (currency_id, course_to_lev) AS
SELECT 1, 0.5 FROM DUAL UNION ALL
SELECT 2, 0.25 FROM DUAL UNION ALL
SELECT 3, 0.125 FROM DUAL;

CREATE TABLE vip_customer (customer_id) AS
SELECT customer_id FROM bank_account WHERE 1 = 0;

Then if you perform an UPDATE:

UPDATE bank_account
SET balance = 200001
WHERE account_id = 3

Then after the UPDATE the VIP customers are:

CUSTOMER_ID
1

fiddle