Trigger to only allow class size/max of 2. Working on some values, but not others

Question

I have this Oracle Sql code where a trigger fires when an insertion is done to the table "Enrollment". What this trigger is supposed to do is when more than 2 students attempt to register for a class it will allow the first two to register but none after that. Currently the code semi works but for some reason it is allowing one class to have more than 2 students.

DROP TABLE enrollment Purge;
DROP TABLE offering Purge;
DROP TABLE faculty Purge;
DROP TABLE course Purge;
DROP TABLE student Purge;


--Create student table

CREATE TABLE student(StdID number(3) constraint student_StdID_PK primary key,
    StdFN varchar2(10),
    StdLN varchar2(15),
    StdCity varchar2(15),
    StdState char(2),
    StdZip number(5),
    StdMajor varchar2(4),
    StdClass char(2),
    StdGPA number(2,1),
        StdBalance NUMBER (12,2));


--create a table for courses and their descriptions

CREATE TABLE course(CourseNo varchar2(8) constraint course_courseNo_PK primary key,
    CrsDesc varchar2(40),
    CrsCredits number(1));


--create faculty table

CREATE TABLE Faculty(FacID number(4) constraint faculty_FacID_PK primary key,
    FacFN varchar2(10),
    FacLN varchar2(15),
    FacDept varchar2(4),
    FacRank varchar2(4),
    FacHireDate date,
    FacSalary number(6),
    FacSupervisor number(4));


--create table for offered courses

CREATE TABLE Offering(OfferNo number(4) constraint offering_OfferNo_PK primary key, 
    CourseNo varchar(8)constraint offering_crs_no references course(courseno), 
    OffTerm varchar2(6),
    OffYear number(4),
    OffLoca varchar(6),
    OffTime varchar(8),
    OffDay varchar(5),
    FacSSN number(4)constraint offering_fac_FK references faculty(facID));


--create table for student enrollment

CREATE TABLE enrollment(StdID number(3),
    OfferNo number(4),
    EnrGrade char(2),
    constraint enrollment_PK primary key (StdID,OfferNo),
    constraint enrollment_std_ID foreign key(StdID) references student(stdID),
    constraint enrollment_class_ID foreign key(offerno) references offering(offerno));
    
    
CREATE OR REPLACE TRIGGER ENROLL_MAX
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
DECLARE
NOFSTUDENTS BINARY_INTEGER;
BEGIN
SELECT COUNT(*) INTO NOFSTUDENTS
FROM ENROLLMENT
WHERE STDID = :NEW.STDID AND ENRGRADE IS NULL;
DBMS_OUTPUT.PUT_LINE(NOFSTUDENTS);
IF (NOFSTUDENTS + 1) > 2
THEN
RAISE_APPLICATION_ERROR(-20003, 'EXCEED MAX NO OF STUDENTS ALLOWED');
END IF;
END;
/

--populate student table

INSERT INTO student values(101,'Joe','Smith','Eau Clare','WI',18121,'IS','FR',3.8,225.25);
INSERT INTO student values(102, 'Rob','King', 'Melrose', 'MN', 56352, 'IS','JR',3.2,120.98);
INSERT INTO student values(103, 'Dan','Robinson', 'Sartell', 'MN', 98042, 'IS','JR',3.6, 36);
INSERT INTO student values(104,'Sue','Williams','St.Cloud','MN',56301,'ACCT','SR',3.2,2386.55);
INSERT INTO student values(105,'Don','Robinson','St.Paul','MN',55103,'MKTG','SR',3.4, 306);


--populate course table

INSERT INTO Course values('CSCI 200','Elements of Computing',3);
INSERT INTO Course values('IS 250','Application of Program Dev. I',3); 
INSERT INTO Course values('IS 251','Application of Program Dev. II',3);
INSERT INTO Course values('IS 454', 'Data Mining for Decision Support',3);
INSERT INTO Course values('IS 356','Systems Analysis and Design I',3);
INSERT INTO Course values('IS 460', 'Project Management',3);
INSERT INTO Course Values('ACCT 291','Accounting Principles II',3);
INSERT INTO Course values('IS 443','Database Design',3);


--poulate faculty table

INSERT INTO faculty values(9001,'Leonard','Vince','IS','ASST','12-Apr-1997',67000,9003);
INSERT INTO faculty values(9002,'Victor','Strong','CSCI','ASSO','8-Aug-1999',70000,9003);
INSERT INTO faculty values(9003,'Nicki','Colan','IS','PROF','20-Aug-1981',75000,9010);
INSERT INTO faculty values(9004,'Fred','Wells','ACCT','ASST','28-Aug-1996',60000,9010);
INSERT INTO faculty values(9010,'Chris','Macon','ACCT','ASST','4-Aug-1980',75000,Null);


--populate offering table
INSERT INTO offering values(2201,'CSCI 200','Spring',2017,'ECC135','10:30AM','MWF',9002);
INSERT INTO offering values(2202,'CSCI 200','Spring',2017,'ECC135','8:00AM','MWF',9002);
INSERT INTO offering values(1102,'ACCT 291','Spring',2017,'CH 14A','2:00AM','MWF',9004);
INSERT INTO offering values(2203,'IS 356','Fall',2017,'CH494','3:30AM','TTH',9001);
INSERT INTO offering values(2204,'IS 251','Fall',2017,'CH494','12:30AM','TTH',9003);
INSERT INTO offering values(1101,'ACCT 291','Fall',2017,'CH350','12:30AM','TTH',9010);
INSERT INTO offering values(2205,'IS 443','Fall',2017,'CH494','9:30AM','MWF',9003);


--populate enrollment table

INSERT INTO enrollment values(101,2201,'A');
INSERT INTO enrollment values(102,2202,'B');
INSERT INTO enrollment values(103,2201,'C');
INSERT INTO enrollment values(103,1101,null);
INSERT INTO enrollment values(104,2202,'A');
INSERT INTO enrollment values(101,2203,null);
INSERT INTO enrollment values(102,2203,null);
INSERT INTO enrollment values(103,2203,null);
INSERT INTO enrollment values(101,1101,null);
INSERT INTO enrollment values(101,2205,null);
INSERT INTO enrollment values(102,2205,null);
INSERT INTO enrollment values(104,2205,null);

When ran Enrollment OFFERNO 2205 does not allow more than 2 students, but Enrollment OFFERNO 2203 does for some reason. Why is that? Thanks in advance.

The trigger is looking at the number of enrolments for a particular student, not the number of enrolments for an offering / course — Gary Myers, Dec 09 '20 at 21:31
This makes sense. I changed my trigger to be WHERE OFFERNO = :NEW.OFFERNO AND ENRGRADE IS NULL; and my problem seems to be resolved thank you @Gary Myers — Boooo402, Dec 09 '20 at 21:44
Just as a side observation, you've defined StdZIP as a number. While zip codes (US) are expressed in only numeric characters, they are not _numbers_. When you store something as a NUMBER, any leading zeros will be dropped. Now, I know that US zip codes never begin with a zero, so in this case it probably won't hurt, but its a bad habit to get into. Remember, regardless of the characters used to express a value, if it makes no sense to do arithmetic with it, then it's not a number - zip code, SSN, phones, etc. — EdStevens, Dec 09 '20 at 21:46
By the way, [the `char` type isn't what you think it is](https://stackoverflow.com/a/42165653/230471) (pet peeve, sorry). — William Robertson, Dec 09 '20 at 22:28
Also, [don't use `varchar` either](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-DF7E10FC-A461-4325-A295-3FD4D150809E). And column names like `FACHIREDATE` are hard to read, so it's a good idea to break the words up with underscores. — William Robertson, Dec 09 '20 at 22:35
@EdStevens What makes you say that U.S. zip codes never begin with a zero? — Matthew McPeak, Dec 09 '20 at 22:54
@MatthewMcPeak - I'd never seen one, but since you raise the point I checked and indeed there are. All the more reason that zip codes should not be NUMBER data types. And add to that the fact that Canadian zip codes definitely have alphabetic characters, storing a zip as NUMBER would limit the app to US only. — EdStevens, Dec 10 '20 at 14:15

score 0 · Answer 1 · answered Dec 09 '20 at 21:38

You seem to want to limit the number of STDID to two per OFFERNO. If so, the where clause of your query is off. You should be filtering on OFFERNO rather than on STDID. So:

CREATE OR REPLACE TRIGGER ENROLL_MAX
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW

DECLARE
    NOFSTUDENTS BINARY_INTEGER;
BEGIN
    
    SELECT COUNT(*) INTO NOFSTUDENTS
    FROM ENROLLMENT
    WHERE STDID = :NEW.OFFERNO AND ENRGRADE IS NULL;
    
    DBMS_OUTPUT.PUT_LINE(NOFSTUDENTS);
    
    IF NOFSTUDENTS = 2 THEN
        RAISE_APPLICATION_ERROR(-20003, 'EXCEED MAX NO OF STUDENTS ALLOWED');
    END IF;
END;
/

I am not sure about the rationale for condition ENRGRADE IS NULL, so I left it as is.

score 0 · Answer 2 · answered Dec 09 '20 at 21:46

I changed my code for the trigger based on what @Gary Myers left below me and this fixed the problem

CREATE OR REPLACE TRIGGER ENROLL_MAX
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
DECLARE
NOFSTUDENTS BINARY_INTEGER;
BEGIN
SELECT COUNT(*) INTO NOFSTUDENTS
FROM ENROLLMENT
WHERE OFFERNO = :NEW.OFFERNO AND ENRGRADE IS NULL;
DBMS_OUTPUT.PUT_LINE(NOFSTUDENTS);
IF (NOFSTUDENTS + 1) > 2
THEN
RAISE_APPLICATION_ERROR(-20003, 'EXCEED MAX NO OF STUDENTS ALLOWED');
END IF;
END;
/

score 0 · Answer 3 · answered Dec 09 '20 at 23:44

Creating a trigger which counts the enrollment table will not work unless you can guaranty only one session will be inserting into this table at once. This is because the query can only ever see rows modified in its own session or rows that have already been committed - if another session has an uncommitted insert then you will end up getting the "wrong" count and allow the insert where you probably didn't want to.

You can force the serialization to happen at the right level - the offering level. You can do this by adding an extra column to your offering table and have your trigger update the appropriate offering row updating this column. Adding a constraint on this column makes the check easy. You can make the column invisible if you so wish.

alter table offering add enroll_cnt number;
alter table offering add constraint offering_enroll_max check (enroll_cnt  <= 2);
create or replace trigger enroll_cnt
before insert or update or delete on enrollment
for each row
begin
  update offering o
  set    enroll_cnt  = enroll_cnt  - 1 
  where  o.offerno = :old.offerno
  and    :old.enrgrade is null;
  
  update offering o
  set    enroll_cnt  = enroll_cnt  + 1 
  where  o.offerno = :new.offerno
  and    :new.enrgrade is null;

end;
/
lock table offering in exclusive mode;
update offering o set o.enroll_cnt = nvl((select count(*) from enrollment e where e.offerno = o.offerno and e.enrgrade is null),0);
commit;

This is also keeping the enrgrade is null filter applying to the count.

Now, when you insert a row into the enrollment table that would effect the count for an offerno, no other sessions will be able to also do anything that would effect the count of the same offerno, nor can they modify the offering table row until the first session ends their transaction.

Trigger to only allow class size/max of 2. Working on some values, but not others

3 Answers3