select count(*) into i from 
 ( select t.name1, count(*) from t
   group by t.name1, t.nom_otd
   having count(*) >  3 
  )

if i >  0  then
 -- есть больше 3-х однофамильцов

select count(*) into i from 
(select g.name1, count(*) over (partition by g.name1, g.otdel) as cnt from g)
where cnt >  3 

create or replace trigger CheckNames before insert or update on t for each row
declare
  i integer;
begin
  select count(*) into i from t where t.name1 = :new.name1 and t.nom_otd = :new.nom_otd;
  if i >=  3  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов (' || :new.name1 || ')');
  end if;
end;

create table G
(
  X     INTEGER,
  NAME1 VARCHAR2( 15 ),
  OTDEL INTEGER
)

create or replace trigger CheckNames after insert on G
declare 
  i integer;
begin

  select count(*) into i from 
    (select g.name1, count(*) over (partition by g.name1, g.otdel) as cnt from g)
  where cnt >  3 ;

  if i >  0  then
    raise_application_error(- 20101 , 'В отделе итак много однофамильцов');
  end if;
end;

insert into g
(
select  1 , 'Сидоров',  1  from dual
union
select  2 , 'Сидоров',  1  from dual
union
select  3 , 'Сидоров',  1  from dual
union
select  4 , 'Сидоров',  1  from dual
)

=
ORA- 20101  'В отделе итак много однофамильцов'