Trigger for inserts works fine, but the one for updates throws an error since I'm trying to get access to its table

I need to make two seperate triggers (it is required to be made that way), one for the inserts and one for the updates on a table contaning different stages of a project which id's comes from another table. Whenever a project stage is updated, I need to make sure the total budget of the stages of a project isn't higher than the one of the actual project. Here is the code of the table creation and the one of the triggers.

CREATE TABLE Projet (
        id_projet integer PRIMARY KEY,
        nom_projet varchar(50) NOT NULL,
        date_debut date NOT NULL,
        date_fin date NOT NULL,
        budget numeric(10,2) NOT NULL
);

CREATE TABLE Projet_phase (
        id_projet integer,
        id_phase integer UNIQUE,
        nom_phase varchar(50) NOT NULL,
        date_debut date NOT NULL,
        date_fin date NOT NULL,
        budget numeric(10,2) NOT NULL,
        PRIMARY KEY (id_projet, id_phase),
        FOREIGN KEY (id_projet) REFERENCES Projet(id_projet)ON DELETE CASCADE
); 

-- Somme budgets phases < budget projet pour projet_phase
CREATE OR REPLACE TRIGGER T_validerbudget_Insertion_projetphase
BEFORE INSERT ON Projet_phase
FOR EACH ROW
DECLARE
    budget_total NUMBER := 0;
    budget_projet Projet.budget%TYPE;

    
BEGIN
    SELECT NVL((sum(budget) + :NEW.budget), 0) total
        INTO budget_total
        FROM projet_phase
        WHERE id_projet = :NEW.id_projet;
    SELECT budget INTO budget_projet FROM Projet WHERE id_projet = :NEW.id_projet;
    IF budget_total > budget_projet THEN
        RAISE_APPLICATION_ERROR(
        -20001,
        'IMPOSSIBLE D''AJOUTER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
        );
    END IF;
END;
/

CREATE OR REPLACE TRIGGER T_validerbudget_Insertion_projetphase
BEFORE UPDATE ON Projet_phase
FOR EACH ROW
DECLARE
    budget_total NUMBER := 0;
    budget_projet Projet.budget%TYPE;

    
BEGIN
    SELECT NVL((sum(budget) + :NEW.budget), 0) total
        INTO budget_total
        FROM projet_phase
        WHERE id_projet = :NEW.id_projet AND id_phase <> :NEW.id_phase;
    SELECT budget INTO budget_projet FROM Projet WHERE id_projet = :NEW.id_projet;
    IF budget_total > budget_projet THEN
        RAISE_APPLICATION_ERROR(
        -20001,
        'IMPOSSIBLE DE MODIFIER LA PHASE CAR LA SOMME DES BUDGETS DES PHASES DÉPASSE CELUI DU PROJET'
        );
    END IF;
END;
/

The first one seems to work perfectly, but the second one gives me this error :

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

I'm very much confused about a few things :

How come only one of them as such and issue instead of both of them not working for this reason
How am I supposed to test information of a table with a trigger if it can't access the data of said table

I tried using the trigger after the update instead, in case the transaction would not be seen as still being processed, therefore giving me access to the table, but it still doesn't. Also tried adding PRAGMA AUTONOMOUS_TRANSACTION as it was mentionned quite often. Howerver it didn't change anything.

Solution

You have FOR EACH ROW triggers. Let's say that you have a project with a budget of 100$ and you have two phases costing 40$ and 50$ so far. Now both phases become more expensive. You must add 10$ to each project:

update projet_phase set budget = budget + 10;

Now the DBMS has a problem: one phase will cost 50$, one 60$, and the sum will exceed the project budget. But you told it to check each row's update and tell you whether it is fine. So if the DBMS updates 40$ to 50$ first, this row is fine, but the other row will get rejected. If, however, the DBMS updates the 50$ to 60$ first, then this row is fine and the 40$ to 50$ update will get rejected. This is exactly what the DBMS is telling you: It cannot apply the trigger and tell you which of the row updates that your update statement runs is prohibited, because the table is in the progress of changing.

The DBMS will not allow the update trigger for any update statement on the table for this reason. This is different with the insert trigger and an INSERT VALUES statement, because this statement can only insert a single row, so the DBMS sees no problem. For an INSERT SELECT statement, though, you'll get the same error.

Demo: https://dbfiddle.uk/anNF8HYt

The easiest solution is to write an after-statement trigger instead. This means mainly omitting the FOR EACH ROW clause (and change BEFORE to AFTER of course). Thus the update/insert statement will run through all the rows and only then will the trigger check the situation.

CREATE OR REPLACE TRIGGER t_validerbudget_projetphase
AFTER INSERT OR UPDATE OF budget ON projet_phase
DECLARE
  v_count INT;
BEGIN
  -- Count the projects with exceeded budget.
  SELECT COUNT(*)
  INTO v_count
  FROM
  (
    SELECT id_projet
    FROM projet p
    JOIN projet_phase pp USING (id_projet)
    GROUP BY id_projet
    HAVING MIN(p.budget) < SUM(pp.budget)
  );

  -- If such projects exist now, throw an exception.  
  IF v_count > 0 THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'La somme des budgets des phases ne doit pas dépasser celle du projet'
    );
  END IF;
END t_validerbudget_projetphase;
/

Demo: https://dbfiddle.uk/TXWMcV__