Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    3

    Question Unanswered: trigger help please

    Hello..

    I am trying to create a trigger that will watch on insert into one table. I know that I can do this other ways - but it has to be a trigger. My situation is this. I have one table that has a list of course numbers and their prerequisites. I have another course that is a list of grades. What has to happen is that when trying to insert into the grades table, it needs to make sure that the student has already taken any prerequisites. I have tried to do this, but it just lets them insert anyway. Here is my code.. any help would be appreciated.

    Prereqs(CNO NUMBER(4) , PreCNo Number(4) <-- table definition
    grade(SSN CHAR(11), CNO NUMBER(4), GRADE CHAR(1) )

    ---TRIGGER ATTEMPT BELOW --

    CREATE OR REPLACE TRIGGER trg_PreReqs
    BEFORE INSERT
    ON Grade
    FOR EACH ROW

    DECLARE
    PreNo NUMBER(4) ;
    OldCourse NUMBER(4) ;
    no_prereq EXCEPTION ;
    BEGIN
    PreNo := 0;
    -- get the pre req if any
    SELECT PreCNo INTO PreNo from PreReqs where CNO = :new.CNO;
    OldCourse := 0;
    IF (( PreNo IS NOT NULL ) AND ( PreNo > 0 )) THEN
    SELECT CNO
    INTO OldCourse
    FROM Grade
    WHERE ssn = :new.ssn
    And cno = PreNo ;
    END If;
    IF (( OldCourse IS NOT NULL ) AND ( OldCourse <= 0 ) ) THEN
    Raise no_prereq ;
    END IF ;

    EXCEPTION
    WHEN no_data_found THEN
    PreNo := 0;

    WHEN no_prereq THEN
    raise_application_error(-20000,'Prerequisite not met for this course.' ) ;

    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help please

    What is happening is that if the student hasn't taken the pre-requisite course, then the SELECT ... INTO OldCourse raises NO_DATA_FOUND, which does nothing.

    Some other comments:
    1) Your code suggests there is only 1 pre-requisite for each course, it won't work if there are 2 or more.
    2) Setting PreNo := 0 in the NO_DATA_FOUND exception is pointless
    3) Perhaps you could combine the 2 selects into one - more efficient:

    SELECT PrecNo FROM PreReqs
    MINUS
    SELECT cno FROM Grade
    WHERE ssn = :NEW.ssn;

    Now if this select DOES return a row it means there is a prerequisite course that the student has not taken. e.g.

    FOR r IN (
    SELECT PrecNo FROM PreReqs
    MINUS
    SELECT cno FROM Grade
    WHERE ssn = :NEW.ssn
    )
    LOOP
    RAISE no_prereq;
    END LOOP;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help please

    Originally posted by Skippy
    Hello..

    I am trying to create a trigger that will watch on insert into one table. I know that I can do this other ways - but it has to be a trigger. My situation is this. I have one table that has a list of course numbers and their prerequisites. I have another course that is a list of grades. What has to happen is that when trying to insert into the grades table, it needs to make sure that the student has already taken any prerequisites. I have tried to do this, but it just lets them insert anyway. Here is my code.. any help would be appreciated.

    Prereqs(CNO NUMBER(4) , PreCNo Number(4) <-- table definition
    grade(SSN CHAR(11), CNO NUMBER(4), GRADE CHAR(1) )

    ---TRIGGER ATTEMPT BELOW --

    CREATE OR REPLACE TRIGGER trg_PreReqs
    BEFORE INSERT
    ON Grade
    FOR EACH ROW

    DECLARE
    PreNo NUMBER(4) ;
    OldCourse NUMBER(4) ;
    no_prereq EXCEPTION ;
    BEGIN
    PreNo := 0;
    -- get the pre req if any
    SELECT PreCNo INTO PreNo from PreReqs where CNO = :new.CNO;
    OldCourse := 0;
    IF (( PreNo IS NOT NULL ) AND ( PreNo > 0 )) THEN
    SELECT CNO
    INTO OldCourse
    FROM Grade
    WHERE ssn = :new.ssn
    And cno = PreNo ;
    END If;
    IF (( OldCourse IS NOT NULL ) AND ( OldCourse <= 0 ) ) THEN
    Raise no_prereq ;
    END IF ;

    EXCEPTION
    WHEN no_data_found THEN
    PreNo := 0;

    WHEN no_prereq THEN
    raise_application_error(-20000,'Prerequisite not met for this course.' ) ;

    END;
    What is happening is that if the student hasn't taken the pre-requisite course, then the SELECT ... INTO OldCourse raises NO_DATA_FOUND, which does nothing.

    Some other comments:
    1) Your code suggests there is only 1 pre-requisite for each course, it won't work if there are 2 or more.
    2) Setting PreNo := 0 in the NO_DATA_FOUND exception is pointless
    3) Perhaps you could combine the 2 selects into one - more efficient:

    SELECT PrecNo FROM PreReqs
    MINUS
    SELECT cno FROM Grade
    WHERE ssn = :NEW.ssn;

    Now if this select DOES return a row it means there is a prerequisite course that the student has not taken. e.g.

    FOR r IN (
    SELECT PrecNo FROM PreReqs
    MINUS
    SELECT cno FROM Grade
    WHERE ssn = :NEW.ssn
    )
    LOOP
    RAISE no_prereq;
    END LOOP;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help please

    Sorry, forgot WHERE clause:

    SELECT PrecNo FROM PreReqs
    WHERE cno = :NEW.cno
    MINUS
    SELECT cno FROM Grade
    WHERE ssn = :NEW.ssn

  5. #5
    Join Date
    Sep 2002
    Posts
    3

    Talking Re: trigger help please

    Thanks... I will give this a shot.. You are correct, I am looking only for one prerequisite. In this case, that is all that is necessary to look for.

    Skippy

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger help please

    Originally posted by Skippy
    You are correct, I am looking only for one prerequisite. In this case, that is all that is necessary to look for.
    I am not sure what you mean by this. If you mean, each course has ONLY ONE prerequisite, then fine.

    If you mean that courses may have MANY prerequisites, but you only need to find ONE prerequisite that the student doesn't have, then yur query was wrong. Your SELECT INTO from PreReqs will raise TOO_MANY_ROWS for any course that has more than one prerequisite.

    Your wording in the original question "make sure that the student has already taken any prerequisites" (plural) suggests that the there can be more than one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •