Limit number of occurrences of value in a table oracle sql-CodePudding

I have oracle sql table of students, students are assigned to groups.

I have to set constraint so there will be no more than 10 students in a group (ideally no more than N students in a group).

Basically restrict table from having more than N rows with the same GROUP_ID value.

Here is table declaration.

    CREATE TABLE  "students" 
       (    "STUDENT_ID" NUMBER(6,0), 
        "GROUP_ID" NUMBER(6,0), 
        "FULL_NAME" VARCHAR2(40), 
         CONSTRAINT "student_id_PK" PRIMARY KEY ("STUDENT_ID")
      USING INDEX  ENABLE
       )
    /
    ALTER TABLE  "students" ADD FOREIGN KEY ("GROUP_ID")
          REFERENCES  "groups" ("GROUP_ID") ENABLE
    /
    CREATE TABLE  "groups" 
       (    "GROUP_ID" NUMBER(6,0), 
        "NAME" VARCHAR2(40), 
         CONSTRAINT "group_id_PK" PRIMARY KEY ("GROUP_ID")
      USING INDEX  ENABLE
       )
    /

I've tried to use trigger that throws error when there is more than 10 students in a group. It worked with inserts but it was useless for updates of table, as i was constantly getting error ORA-04091: table students is mutating

Here is shortened version of the trigger:

SELECT count(*) INTO stud_in_g 
    FROM "students" 
    WHERE GROUP_ID = :NEW.GROUP_ID; 
    
IF stud_in_g>10 THEN 
    raise_application_error(-20001, 'to many students'); 
END IF;

I've read that oracle forbids to read from table when it is updating. If that's so then how can i ensure that there can't be more than 10 students in a group?

CodePudding user response：

The "shortened version" of the trigger doesn't show what type of trigger it is - most probably a row level trigger. That won't work because of the mutating table trigger error. A statement level trigger does work:

create table test_table (
    id                             number generated by default on null as identity 
                                   constraint test_table_id_pk primary key,
    name                           varchar2(100 char),
    the_group                      varchar2(100 char)
);

Table TEST_TABLE created.

BEGIN
insert into test_table (name, the_group ) values ('Chandler', 'A');
insert into test_table (name, the_group ) values ('Ross', 'A');
insert into test_table (name, the_group ) values ('Joey', 'A');
insert into test_table (name, the_group ) values ('Phoebe', 'B');
insert into test_table (name, the_group ) values ('Jennifer', 'B');
insert into test_table (name, the_group ) values ('Monica', 'B');
commit;
END;
/

PL/SQL procedure successfully completed.

CREATE OR REPLACE TRIGGER test_table_as 
    AFTER UPDATE OR INSERT ON test_table  
DECLARE
    l_max_members NUMBER := 3;
BEGIN
    -- determine the transaction type
    FOR r IN (SELECT
                the_group,
                COUNT(*) AS cnt
                FROM
                test_table
               GROUP BY
                the_group
              HAVING
                COUNT(*) > l_max_members)
    LOOP
      IF r.cnt > l_max_members THEN
        raise_application_error(-20100,'Max '||l_max_members||' per group');
      END IF;
    END LOOP;
END;
/


Trigger TEST_TABLE_AS compiled

insert into test_table (name, the_group ) values ('Jill', 'B');

Error starting at line : 39 in command -
insert into test_table (name, the_group ) values ('Jill', 'B')
Error report -
ORA-20100: Max 3 per group
ORA-06512: at "KOEN.TEST_TABLE_AS", line 8
ORA-06512: at "KOEN.TEST_TABLE_AS", line 8
ORA-04088: error during execution of trigger 'KOEN.TEST_TABLE_AS'