I have a small table looking like this

```
table People
(  name VARCHAR(20) PRIMARY KEY
   ,group NUMBER(4) 
);

```

And i need to create trigger (or triggers) thats will allow below rules to work:  

 - 1 if there are more then 10 **names** in **group** i need to raise an error if someone tries to INSERT next people for this group.  

 - 2 if INSERT comes with NULL value for **group** field i need to assign it to group which count is less then 10.  

 - 3 if there are 10 **names** in all **groups** i need to generate next **group** number.  

 - 4 I need to avoid **mutating table** error.

This is what i've done till now :

```
CREATE OR REPLACE TRIGGER people_bis
BEFORE INSERT ON people
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
g_count NUMBER(4);
g_num NUMBER(4);
g_l NUMBER(4);
g_r NUMBER(4);

BEGIN

Select count(*) into g_count from people where group = :new.group;
If g_count > 9 Then
raise_application_error (-20003,'Group reached it limit, please choose other');
End if;
If :NEW.group = '' or :NEW.group is null Then
 select count (*) into g_l from (select count(imie),group from people group by group having count(name) = 10);
select count (distinct group) into g_r from people;
    if g_l = g_r then
    select max(group)+1 into g_num from people;
    else
    select group into g_num from(select group, count(name) from people having  count(name) < 10 group by group order by count(group) desc) where rownum < 2;
    End if;
:New.group := g_num;
End if;
End people_bis;

```

Code above works, but when i INSERT as select from a mirror table e.g.  

`INSERT INTO people(name) select concat(name,'_next') from people_mirror;`
 The result is that it exceed the given limit (10) for a group.
 Also i know that using **PRAGMA AUTONOMOUS\_TRANSACTION** is not a best way to avoid mutating table error, and i know i could reach this functionality if i would split row trigger into statement triggers but i'm just out off idea how to get it.

So anyone? ;)

Thanks in advance.

**-------------------EDIT------------------------**

Those are the triggers that works but still i have doubts about them as both of them are BEFORE and row type.

```
CREATE OR REPLACE TRIGGER people_bir1
BEFORE INSERT on people
FOR EACH ROW
DECLARE 
V_count NUMBER(2);
BEGIN
   If :NEW.group = '' or :NEW.group is null then
   return;
   end if;
insert into groups values(:New.group,1);
exception when dup_val_on_index then
Select count into v_count from groups where group = :New.group;
UPDATE groups set count = v_count+1 where group = :New.group;

END people_bir1;      

CREATE OR REPLACE TRIGGER people_bir2
BEFORE INSERT on people
FOR EACH ROW
DECLARE
g_count NUMBER(2);
g_num NUMBER(2);
begin
   if :NEW.group = '' or :NEW.group is null Then
   select min(count) into g_count from groups;
       if g_count = 10 Then
       select max(group) into g_num from groups;
       g_num := g_num+1;
       Else
       select min(group) into g_num from group where count = g_count;
       End if;
   :New.group := g_num;
   Else
   select count into g_count from groups where group=:New.group;
   if g_count > 9 then 
   raise_application_error (-20003,'More then 10 people in group please select another');
   end if;
   end if;
end people_bir2;

```

as it is too long i couldn't paste it as a comment to @TonyAndrews answer.