Re: help with database

Comments embedded.
TekDragon wrote:
> Thanks that did the trick.
>
> I forgot about the max function. Why the +1? I tried it with out that and it
> works.

Because you don't have customer_id declared as a primary key, so you can have duplicate entries in your table. This is what your current trigger produces.

> My next question is what would happen if this application is used
> accross multiple computers at the same time? Would the correct customer_id
> by identified with the correct customer?
>

No, and with the code you're implementing a single user won't get a correctly identified customer, either. The max(col) + 1 increments the current highest value in the col and (hopefully) prevents duplicates. Of course, it won't once you have more than one user connected. Any select against the target table will produce duplicates between sessions as the 'select' can only 'see' what has been committed, not what is in process. A sequence has no such issues.

> "onedbguru" <onedbguru_at_firstdbasource.com> wrote in message
> news:1145390327.156831.163740_at_v46g2000cwv.googlegroups.com...
> > you are selecting ALL customer_ids into :new.customer_id...
> >
> > not complete syntax
> > create sequence newcustseq.....
> >
> > create or replace trigger wine_cust_trigger
> > before insert on wines
> > for each ro
> > begin
> > select newcustseq.nextval into :new.customer_id from dual;
> > end ;
> > /
> >
> > OR
> >
> > this very poor-man's sequence that is not very scalable due to locking
> > etc..
> > create or replace trigger wine_cust_trigger
> > before insert on wines
> > for each ro
> > begin
> > select max(customer_id)+1 into :new.customer_id from customer ;
> > end ;
> > /
> >

I'd be using a sequence and a trigger to ensure you have unique entries across sessions. I would also make your customer_id your primary key, so it's both unique and not null; this, of course, would cause your current trigger to fail on insert, and it may cause the creation of the constraint to generate errors as you have multiple records with the exact same customer_id (this is the problem your current trigger creates).

You asked for assistance; it's foolish of you to not take that which is offered.

David Fitzjarrell Received on Tue Apr 18 2006 - 15:56:25 CDT