Trigger avoiding mutating table and updating :new.values

Question

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.

Answer 1

You are right that adding PRAGMA AUTONOMOUS_TRANSACTION is no solution. One way to do this is to maintain a count of people per group in the GROUPS table (if you don't have a GROUPS table then you could add one) using triggers on PEOPLE:

• After INSERT on PEOPLE: update GROUPS, add 1 to count of group they are in
• After DELETE on PEOPLE: update GROUPS, subtract 1 from count of group they are in
• After UPDATE on PEOPLE: update GROUPS, add 1 to new group, subtract 1 from old group

Then your BEFORE INSERT trigger doesn't need to look at the PEOPLE table, it can look at GROUPS:

Select people_count into g_count from groups where group = :new.group
for update;

Note the for update clause to lock the GROUPS row until your transaction completes.

Answer 2

You can use a compund trigger. It looks like this:

CREATE OR REPLACE TRIGGER people_bis
   FOR INSERT ON people
COMPOUND TRIGGER

g_count NUMBER(4);
g_num NUMBER(4);
g_l NUMBER(4);
g_r NUMBER(4);

BEFORE STATEMENT IS
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;

   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;

END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN

   If :NEW.group = '' or :NEW.group is null Then
   :New.group := g_num;
   End if;
END BEFORE EACH ROW;

End people_bis;

Please note, most probably this code does not work as you wanted but it should give you a general impression how to work with a Compound Trigger.