/* a trigger that prevents the insertion of a tuples 
   for a grade 'I' into the transcripts table 
   for classes with no enrollment allowed */ 

/* start out with cleaning the older version if the 
   trigger */

drop trigger CheckMaxEnroll;

/* the trigger name is CheckMaxEnroll */ 
/* it is activated before the insertion is done */ 
/* on the transcripts table */ 
/* the new row is referred to as N */
/* this will be done for each row */ 

create or replace trigger CheckMaxEnroll
         before insert
         on transcripts
            referencing new as N
         for each row

/* declare the variables */ 

DECLARE
        x number(10);           
        Some_Error EXCEPTION;

BEGIN

    /* getting the value of maxenroll into x */ 

    select maxenroll into x
    from schedule S
    where :N.crscode = S.crscode
           and :N.semester = S.semester;

    /* do something with the new row:
       here, we check if it is an incomplete grade 
             and the maximal enrollment for 
             the class is 0 */ 

    if (:N.grade = 'I' and x = 0) then
        RAISE Some_Error;
    end if;

EXCEPTION
   WHEN Some_Error THEN
        Raise_application_error(-20000, 'Error registeration over occurred');
    return;

end CheckMaxEnroll;
.
run;
