More trigger hints

The code fragments given here are incomplete and some vital parts are missing (on purpose!). However, if you're stuck you might find it useful to study these code fragments.

CREATE OR REPLACE TRIGGER CourseRegistration
INSTEAD OF INSERT ON RegisteredView
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
maxNum Int;
currentNum INT;
limited INT;
BEGIN
   SELECT COUNT (*) INTO limited FROM LimitedCourses WHERE code = :new.course;
   IF limited > 0 THEN --limited course
      SELECT nrStudent INTO maxNum FROM LimitedCourses WHERE code = :new.course;
      SELECT COUNT (*) INTO currentNum
      FROM Registered
      WHERE course = :new.course;
      IF currentNum < maxNum THEN --still has a place
         INSERT INTO Registered...
      ELSE --the course is full
         INSERT INTO WaitingList... --remember to process the priority
      END IF;
   ELSE --normal course
      INSERT INTO Registered...
   END IF;
END;

==============

CREATE OR REPLACE TRIGGER CourseUnregistration
INSTEAD OF DELETE ON RegisteredView
REFERENCING OLD AS old
FOR EACH ROW
DECLARE
firstStuInQueue Students.ID%TYPE;
waitingNum INT;
BEGIN
   DELETE FROM Registered...
   SELECT COUNT (student) INTO waitingNum FROM WaitingList WHERE course = :old.course;
   IF waitingNum > 0 THEN --there are waiting students
      SELECT student INTO firstStuInQueue
      FROM WaitingList WHERE... --first priority
      INSERT INTO Registered... --first student in the queue
      DELETE FROM WaitingList... --first student in the queue
      --UPDATE FROM WaitingList... --update the priority if necessary
   END IF;
END;

Trigger compilation errors

If your trigger is "created with compilation errors", you can get additional information about the errors by typing:

    SHOW ERRORS;

See Ullman's notes on displaying trigger definition errors.

Raising errors within triggers