Re: trigger help please

"Skippy" <SkippyNOSPAM_at_hotmail.com> wrote in news:anarts$3af$1_at_slb3.atl.mindspring.net:

> This was my attempt at the trigger.. but it does not work.. no error
> code.. but it lets me insert data that I should not be allowed to.
>
> 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;
If the student had not taken the prereq would you not get a notfound on the

     SELECT CNO
      INTO OldCourse
      FROM Grade
      WHERE  ssn = :new.ssn
       And  cno = PreNo  ;

Which then just goes into your exception handler and sets preNo to 0 but doesn't raise any exception.

Another warning: If you try to do and "insert ... (select ...)" on this table you're going to get the dreaded mutating table error.

-- 
Ken Denny
http://www.kendenny.com/