Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table Person (ID number primary key, Surname varchar2(16), Name varchar2(16), Nationality varchar2(3))

insert all
into person values (1000, 'IN SOCKS', 'FOX', 'UK')
into person values (2000, 'I AM', 'SAM', 'US')
into person values (3000, 'KNOX', 'MR', 'UK')
into person values (4000, 'MAYZIE', 'DAISYHEAD', 'JP')
into person values (5000, 'IN THE HAT', 'THE CAT', 'US')
into person values (6000, 'ONE', 'THING', 'FR')
into person values (7000, 'TWO', 'THING', 'FR')
into person values (8000, 'MOONEY', 'MARVIN K', 'JP')
select * from dual;
8 rows affected

create table Project (Name varchar2(16) primary key, Manager references person, StartingYear number, NumPeopleInvolved number, International varchar2(1))

insert all
into project values ('ZOO', 3000, 2020, 3, 'Y')
into project values ('VMIR', 2000, 2020, 2, 'N')
into project values ('TIDY UP', 5000, 2020, 3, 'Y')
select * from dual;
3 rows affected

create table Personnel (Project references project, PersonID references person)
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;

/
select * from all_errors where name = upper('international_project_trg');
insert into personnel values ('ZOO', 3000);
1 rows affected

insert into personnel values ('ZOO', 1000);
ORA-20999: ZOO must have multi-national team membership
ORA-06512: at "FIDDLE_SUUCJMUMRUNIEEPGFMUY.INTERNATIONAL_PROJECT_TRG", line 43
ORA-04088: error during execution of trigger 'FIDDLE_SUUCJMUMRUNIEEPGFMUY.INTERNATIONAL_PROJECT_TRG'

insert into personnel values ('ZOO', 2000);
1 rows affected

insert into personnel values ('ZOO', 1000);
1 rows affected

insert all
into personnel values ('TIDY UP', 5000)
into personnel values ('TIDY UP', 6000)
into personnel values ('TIDY UP', 7000)
into personnel values ('VMIR', 4000)
into personnel values ('VMIR', 8000)
select * from dual;
5 rows affected