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 projects(
             proj_id     integer 
           , name        varchar2(128)   
           );

 
create table employees(
             emp_id    integer 
           , proj_id   integer
           ); 
insert into projects( proj_id, name) 
  select 1, 'Validate Formula E=mc^2'       from dual union all
  select 2, 'Rollout for Manhattan Project' from dual union all
  select 3, 'OOPS' from dual ;
3 rows affected

create or replace trigger limit_3_emp_per_proj_ais
           after insert on employees 
declare
   k_new_msg_line    constant            varchar2(3)  := chr(10) || '  ';
   k_max_emp_message constant            varchar2(80) := 
       'No rows inserted!' || k_new_msg_line || 'Following Projects have exceed max of 3 employees:'; 
   cursor proj_over_3_emp is
         select proj.name, proj.proj_id, count(*)
           from projects  proj 
           join employees emp
             on (emp.proj_id = proj.proj_id)
          group by proj.proj_id, proj.name    
         having count(*) > 3 
          order by proj.proj_id;
          
   l_proj_exceeds_3_emp                varchar2(3500) := null;           
begin 
   for proj_emp in proj_over_3_emp  
   loop 
       l_proj_exceeds_3_emp := l_proj_exceeds_3_emp || k_new_msg_line ||
                               proj_emp.name || '(' || proj_emp.proj_id || ')';
   end loop; 
   
   if l_proj_exceeds_3_emp is not null
   then 
      raise_application_error( -20199,k_max_emp_message || l_proj_exceeds_3_emp);
   end if;
                               
end limit_3_emp_per_proj_ais;
/
 
insert into employees(emp_id, proj_id)
  select 1,1 from dual union all 
  select 2,1 from dual union all 
  select 3,1 from dual;
3 rows affected

select * from employees;


EMP_ID
PROJ_ID


1
1

2
1

3
1


  
insert into employees(emp_id, proj_id)  
  select 4,1  from dual union all 
  select 3,2  from dual union all
  select 6,2  from dual union all 
  select 7,2  from dual union all
  select 8,3  from dual union all 
  select 9,3  from dual union all 
  select 10,3 from dual union all 
  select 11,3 from dual ;
ORA-20199: No rows inserted!
  Following Projects have exceed max of 3 employees:
  Validate Formula E=mc^2(1)
  OOPS(3)
ORA-06512: at "FIDDLE_JUMQUHRMKRYSPJUAXJCI.LIMIT_3_EMP_PER_PROJ_AIS", line 24
ORA-04088: error during execution of trigger 'FIDDLE_JUMQUHRMKRYSPJUAXJCI.LIMIT_3_EMP_PER_PROJ_AIS'

select * from employees;


EMP_ID
PROJ_ID


1
1

2
1

3
1