I'm designing a class registration system for a project for school. One of the requirements is to be able to add the instructors through the form but the instructor id number must be system assigned and increment by a determined value, call it 1, for the sake of arguments. I've tried the when-new-record function but haven't been able to get the code right to make it work. Any assistance would be much appreciated.
Well I didn't got you completly...Are u selecting it from a system sequence? or whats the case..if you say in detail then may be i will be able to help you...or if u can send me the form i willl be try to look into it..!
Use pre_insert on the block:
select nvl(max(inst_id),0)+1 into :inst_id from instructors ;
as long as only one person will ever be connected to your form at a time (single user application), then abdali's solution is ok. Otherwise, don't do that. You'll eventually end up with duplicates (or errors depending upon whether uniqueness will be enforced by DB).
If you need to deal with multiple connections, use a sequence, as suggested by nabeel. A sequence is a DB object which guarantees unique, incremented values no matter how many people access it simultaneously. To create a sequence:
create sequence myseq nocache;
to use the values, do as abdali suggested, and update the value in the pre-insert trigger on that block:
select myseq.nextval into :myblock.inst_id from dual;
Click Here to Expand Forum to Full Width