How to solve this exercise about Oracle Triggers

When you have a row-level trigger on table Personnel then you cannot run any SELECT on table Personnel within the trigger - you will get an ORA-04091: table PERSONEL is mutating ... error.

I think your teacher is expecting something like this:

CREATE TRIGGER ProjectConsistency
    BEFORE INSERT OR UPDATE ON PROJECT
    FOR EACH ROW
    
    p_count INTEGER;
    n_count INTEGER;

BEGIN

    SELECT COUNT(*)
    INTO p_count
    FROM Personnel
    WHERE PROJECT = :new.NAME;
        
    IF :new.NumPeopleInvolved <> p_count THEN
        RAISE_APPLICATION_ERROR(-20010, 'The number of people involved in a project must be consistent with the number of tuples entered in Personnel for that project');
    END IF;

    IF :new.International = 'YES' THEN
        SELECT COUNT(DISTINCT Nationality)
        INTO n_count
        FROM Personnel
        WHERE PROJECT = :new.NAME;
        
        IF n_count < 2 THEN
            RAISE_APPLICATION_ERROR(-20010, 'The project must involve at least two people of different nationalities')
        END IF;    
    END IF;

END;

In reality you would not implement such requirement with a trigger, you would use a PL/SQL procedure.

Attribute NumPeopleInvolved is useless, i.e. redundant. Typically you would solve it by

UPDATE PROJECT proj 
SET NumPeopleInvolved = 
    (SELECT COUNT(*)
    FROM Personnel p
    WHERE PROJECT = :new.NAME)
WHERE NAME = :new.NAME;

Such an update could be done by a trigger, for example.

Actually you would need similar triggers also on table Personnel and Person, because the personel/persons may change and project would become inconsistent. I don't know whether this should be considered by the exercise.

Imagine, on person gets released, i.e. deleted from table Person:

• would the application raise an error - person cannot be released (what happens if the person dies by Corona :-))?
• would be project be invalid?
• would be project be automatically updated?

Then, you should never raise errors like raise_application_error(-1) - always let the user know what went wrong!

The best way to do this is with a compound trigger. With a compound trigger we avoid the problem of mutating tables which we would get from a row level trigger on PERSONNEL.

We keep track of each project which is referenced by each affected row in a DML statement (insert, update, delete) in an array. At the end of the statement we query those projects to find whether the project is international, and if it is to check the nationalities of its assigned personnel.

It might look like this:

CREATE OR REPLACE TRIGGER international_project_trg
  FOR insert or update or delete ON personnel
    COMPOUND TRIGGER

  -- Global declaration
  type project_t is table of number index by personnel.project%type;
  g_project project_t; 

  BEFORE EACH ROW IS
  BEGIN
    CASE
      -- we don't care about the value here, we just what a set of distinct projects
      WHEN INSERTING THEN
        g_project(:new.project) := 1;
      WHEN UPDATING THEN
        g_project(:new.project) := 1;
      WHEN DELETING THEN
        g_project(:old.project) := 1;
    END CASE;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
    l_project personnel.project%type;
    l_country_cnt pls_integer;
    l_people_cnt pls_integer; 
  BEGIN
    l_project := g_project.first();
    
    while l_project is not null loop
      select count(distinct ppl.nationality)
             ,count(*) 
       into l_country_cnt
            ,l_people_cnt
       from personnel per
            join project prj on per.project  = prj.name
            join person  ppl on per.personid = ppl.id     
        where per.project = l_project
        and   prj.international = 'Y';
        
        if l_people_cnt <= 1 then
          -- either not international project or only one assigned person
          -- so we don't care
          null;
        elsif l_country_cnt <= 1 then
          raise_application_error(-20999, l_project ||' must have multi-national team membership');  
        end if;
        
        l_project := g_project.next(l_project);
        
    end loop;    
    
  END AFTER STATEMENT;

END international_project_trg;

Here is a working demo on db<>fiddle. You can see that although the trigger allows for an international project to have only one assigned person it throws an error when we add a second person of the same nationality. We can solve this by inserting rows in a special order, or better by inserting a set of rows. This is a problem with apply such business rules.

You can use the same approach (in the same trigger) to check that whether the number of assigned personnel meets the Project.NumPeopleInvolved rule.

Note: compound triggers arrived in Oracle 11gR1.

I think the following should work with insertions, deletions and updates on table Personnel. It simply check and update the international consistency for each project whether the table Personnel is altered.

CREATE TRIGGER UpdateInternationalProject
AFTER INSERT OR UPDATE OR DELETE ON Personnel
BEGIN
    SELECT name, international
    FROM Project
    AS ProjectInternational;

    FOR projectInfo IN ProjectInternational
    LOOP
        SELECT COUNT(DISTINCT nationality)
            AS numNationalities
        FROM Personnel INNER JOIN Person
        ON personId = id
        WHERE project = projectInfo.name;

        IF numNationalities = 1 THEN
            IF projectInfo.international THEN
                UPDATE Project
                SET international = 0
                WHERE name = projectInfo.name;
            END IF;
        ELIF numNationalities > 1 THEN
            IF NOT projectInfo.international THEN
                UPDATE Project
                SET international = 1
                WHERE name = projectInfo.name;
            END IF;
        END IF;
    END LOOP;
END;