Re: PL/SQL questions

piyush rampuria wrote:

> I have two tables
>
> Enroll (StudentID, courseId, paymentoption)
> Bills (bill_no, studentid, courseid, coursefees, interest, total)
>
> the primary key of enroll (studentid + courseId) is the foreign key of
> bills.
> the primary key of bills is bill_no which is a sequence
>
> now the problem.
>
> I have write a program which will do the following
>
> When one inserts an enroll record, the program should insert a bill
> for that student itself. and it should be complicated enough to tackle
> the payment option thing, as in if the paymentoption is cash then give
> two percent discount based on that calculate totals.
> otherwise not.
>
> Notice that the coursefees attribute of bills should be derived from
> the table courses(courseid, coursefees)
>
> In plsql i tried to write a trigger for enroll dept which satisfies
> certain conditions. The problem is that if a tuple passes the
> constraints in the trigger then... i get an error that says that
> violates the foreign key constrain of bills table. and to think of it
> it makes sense, because I called the procedure insert bill from the
> trigger itself....so even before the enroll record is inserted in
> enroll table it tries to insert that record in bills table and that
> gives me a foreign key violation. does anyone know how to come around
> this problem???
>
> Thanks in advance
> piyush
>
> may be i m trying to do too much....

I think this is a school project and you need to work this out for yourself.

But you should reconsider your analysis of what is causing the problem.

Nothing exists in any table until a commit is issued. And there are options like deferrable constraints and autonomous transactions that may or may not be relevant.

Daniel Morgan Received on Mon Apr 29 2002 - 12:42:27 CDT