Re: trigger help please

From: Skippy <SkippyNOSPAM_at_hotmail.com>
Date: Mon, 30 Sep 2002 20:57:46 -0400
Message-ID: <anarts$3af$1@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;
"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D986E41.51F9D202_at_exesolutions.com...
> Michael Kuznetsov wrote:
>
> > Sorry Skippy, i've send you answer what belongs to others. About your
> > question. I think you can solve your problem without database trigger.
> > Oracle check links between tables automatically if you create foreign
> > key constraint.
> >
> > Michael Kuznetsov
> >
> > mvk_at_servocomp.ru (Michael Kuznetsov) wrote in message
news:<1543a3e7.0209300004.59117476_at_posting.google.com>...
> > > Hi Skippy,
> > >
> > > There is very old but easy way to copy table from one database to
> > > another. It's using COPY command from SQL*Plus.
> > >
> > > It is fragment from SQL*Plus documentation:
> > >
> > > The following command copies the entire EMP table to a table named
> > > WESTEMP.
> > > Note that the tables are located in two different databases. If
> > > WESTEMP already
> > > exists, SQL*Plus replaces the table and its contents. The columns in
> > > WESTEMP have the same names as the columns in the source table, EMP.
> > >
> > > SQL> COPY FROM SCOTT/TIGER_at_HQ TO JOHN/CHROME_at_WEST ->
> > > REPLACE WESTEMP ->
> > > USING SELECT * FROM EMP
> > >
> > >
> > > Regards,
> > > Michael
> > > Brainbench MVP for Oracle Programming
> > > http://www.brainbench.com
> > >
> > >
> > > "Skippy" <SkippyNOSPAM_at_hotmail.com> wrote in message
news:<an86s2$3mo$1_at_slb7.atl.mindspring.net>...
> > > > I need to some help creating a trigger. I have a table and I need an
insert
> > > > trigger when I try to insert something in table 1.. it makes sure
that the
> > > > value exists in table 2.. more sepcifically.
> > > >
> > > > Table 2 is a list of courses and pre-requisites for each course.
> > > >
> > > > Table 1 is a registration table. The user should not be able to
sign up for
> > > > a course if they have not taken the pre-requistie course
> > > >
> > > > Table 3 has the list of courses that the student has taken.
> > > >
> > > > thanks.
> > > >
> > > > skippy
>
> What you likely have missed is that Skippy is a student ... and proposing
an alternative solution will not cut the
> mustard with the instructor.
>
> Skippy, what you need is a trigger on the registration table as you
suggest. But you have demonstrated no effort on
> your part to write one short of apparently asking someone to do it for
you.
>
> Go to http://tahiti.oracle.com and look up "BEFORE INSERT TRIGGER." There
you will find instructional material and
> examples though I would suspect that you have them in your textbook and
examples from class.
>
> If, after writing your trigger and attempting to debug any errors you are
still having problems ... post your
> efforts here as well as the full and complete ORA-#### error message and
text. Then someone will help you fix it.
>
> Daniel Morgan
>
Received on Mon Sep 30 2002 - 19:57:46 CDT