Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employees (
	employee_id number primary key,
	manager_id number
);
begin
    insert into employees values(1, null);
    insert into employees values(2, 1);
    insert into employees values(3, 1);
    insert into employees values(4, 1);
    insert into employees values(5, null);
    insert into employees values(6, 5);
end;
/
1 rows affected

create table employee_manager_cnt (
	manager_id          number(12) not null primary key,
	cnt          		integer not null check (cnt <= 3)
);
insert into employee_manager_cnt(manager_id, cnt)
select manager_id, count(*) from employees where manager_id is not null group by manager_id
2 rows affected

select * from employee_manager_cnt;


MANAGER_ID
CNT


1
3

5
1


create or replace trigger trg_employee_manager_cnt
	after insert or delete or update of manager_id
	on employees
	for each row
begin

	if updating or deleting then
		merge into employee_manager_cnt t
		using dual
		on ( t.manager_id = :old.manager_id )
		when matched then
			update set t.cnt = t.cnt - 1
			delete where t.cnt = 0
		;
	end if;

	if inserting or updating then
		merge into employee_manager_cnt T
		using dual
		on ( t.manager_id = :new.manager_id )
		when matched then
			update set t.cnt = t.cnt + 1
		when not matched then
			insert (manager_id, cnt) values (:new.manager_id, 1)
		;
	end if;
end;
/
insert into employees values(4, 1);
ORA-00001: unique constraint (FIDDLE_IPKLUQRLMTXQYWZOZSCI.SYS_C0089168) violated

insert into employees values(10, 5);
1 rows affected