SQL Trigger issue : Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Question

Hi guys, I'm facing an issue trying to auto update a table with triggers.

I'm developing a real estate management app and I'd like to generate, for the property manager side, a table (bill) with all charges and payments received per year and per landlord for a same shared building.

The main tables that I use for this part are:

• building
• charge (which contains building_id)
• charge_payment(which contains building_id and landlord_id as user_id)

To generate this table (bill), for exemple, I made a trigger on table charge which will generate a table on each new charge insert that I insert into bill table. I check in table charge and table charge_payment for that.

Here is how it looks like:

CREATE DEFINER=`root`@`localhost` TRIGGER `charge_AFTER_INSERT` AFTER INSERT ON `charge` FOR EACH ROW BEGIN
        
    INSERT INTO bill(building_id, user_id, year, amount, payment)
    SELECT
        p.building_id,
        ll.user_id user_id,
        bcharge_annual.year year,
        ROUND(sum(p.building_slice) * (bcharge_annual.amount), 2) amount,
        IFNULL(total_payments.payments, 0) payment
    FROM landlord ll
    LEFT JOIN property p ON ll.property_id=p.id
    LEFT JOIN (
        SELECT 
            c.building_id building_id,
            c.year,
            SUM(c.amount) amount
        FROM charge c
        WHERE c.building_id=new.building_id and c.property_id IS NULL
        GROUP BY c.year
        ) bcharge_annual ON p.building_id=bcharge_annual.building_id
    LEFT JOIN (
        SELECT 
            cp.user_id user_id, 
            cp.corresponding_year year, 
            IFNULL(sum(cp.amount), 0) payments
        FROM charge_payment cp 
        WHERE cp.corresponding_year=new.year
        GROUP BY user_id,corresponding_year
        ) total_payments ON ll.user_id=total_payments.user_id
    WHERE p.building_id=new.building_id AND (ll.ownership_end IS NULL OR ll.ownership_end > (SELECT NOW())) AND bcharge_annual.year=new.year
    GROUP BY ll.user_id,year;
    
END

My problem is that if I add an other charge on the same building and same year, I had an error. Until now, I solved the problem by deleting all raws in table bill with same year before each new charge insert, but it's quite dirty :)

CREATE DEFINER=`root`@`localhost` TRIGGER `charge_AFTER_INSERT` AFTER INSERT ON `charge` FOR EACH ROW BEGIN
    DECLARE matchbillyear INT;
    
    SELECT COUNT(*) 
    INTO matchbillyear
    FROM bill b
    WHERE b.building_id=new.building_id AND b.year=new.year;
    
    IF matchbillyear > 0 THEN
        DELETE FROM bill bil
        WHERE bil.year=new.year;

    ...

So I've been thinking, and I tried to remove this deletion trigger on table charge and add a trigger on table bill to check before insert on each row if it should be an insert or just an amount update:

CREATE DEFINER=`root`@`localhost` TRIGGER `bill_BEFORE_INSERT` BEFORE INSERT ON `bill` FOR EACH ROW BEGIN
    DECLARE matchBillId INT;
    
    SELECT b.id 
    INTO matchBillId
    FROM bill b
    WHERE b.building_id=new.building_id AND b.year=new.year AND b.user_id=new.user_id;
    
    IF EXISTS (
        SELECT b.id 
        FROM bill b
        WHERE b.building_id=new.building_id AND b.year=new.year AND b.user_id=new.user_id) THEN
        UPDATE bill
        SET NEW.amount = amount + NEW.amount
        WHERE id = matchBillId;
    END IF;
  
END

Was looking good to me, but definitely not for MySql which throws me this error:

ERROR 1442: 1442: Can't update table 'bill' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
SQL Statement:
INSERT INTO `erem_pro`.`charge` (`building_id`, `year`, `type`, `amount`) VALUES ('1', '2021', 'Entretien', '1300.00')

If anyone has any clue on how to make it work, would be great. Otherwise I think I'll have to rethink my app... :/

Thanks a lot in advance and congratulation if you read all that :)