Re: HELP: How to generate sequential Primary Keys on a maste

Sequence seems the instruction you need in that matter: CREATE SEQUENCE sequence

   INCREMENT BY .. START WITH .. MINVALUE .. MAXVALUE .. CYCLE ... [NO]CACHE       sequence.NEXTVAL (+increment), seq.CURRVAL

"IF" <tlfbi98_at_terra.es> wrote in message news:8up6bug9dim0caes9bob8laq5f9lvga4o9_at_4ax.com...
> First of all, thanks a lot for your answers and my apologies for
> posting the message and not replying until today. I've been out of
> town. Sorry...
>
> Well, I've been reading the answers and the problem is very similar to
> what Philip Chee points out. The sequential numbers are needed for
> invoice numbers. My customer needs sequential invoice numbers (without
> gaps) that reinitialize every year. That is, first invoice form year
> 2002 will have the invoice-number 2002-1. The second one will have the
> invoice_number 2002-2, and so on. When year 2003 arrives, the first
> invoice will have the invoice_number 2003-1; the second one will have
> 2003-2, and so on...
>
> This is strictly how the customer wants it to be. So, anyone can help
> me (giving exhaustive details, please) on how to achieve this by
> making use of a table that keeps the last invoice number used?. How
> and where do I have to lock that table? What happens if the form has
> to be a multi-record master-detail one? (that is, the customer wants
> to use the form for entering (in the same transaction) several
> invoices (multi-record master block) which have several line items
> each (multi-record detail))
>
> Thank you very much again
> Best regards.
>
> On Sat, 06 Apr 2002 15:05:30 GMT, philip_at_aleytys.pc.my (Philip Chee)
> wrote:
>
> >In article <6asrau4lfelqqtptocr3h3hqbt1ccdncth_at_4ax.com>
postbus_at_sybrandb.demon-verwijderdit.nl writes:
> >>On Mon, 01 Apr 2002 16:09:51 GMT, IF <tlfbi98_at_terra.es> wrote:
> >
> >>>We cannot use Oracle Sequences because we cannot lose any value in the
> >>>sequence. Therefore, in order to generate the sequential values for
> >>>the primary key, we have to use a value stored in another table.
> >
> >>rubbish. Create sequence ... nocache ordered.
> >
> >So, um will a transaction rollback also rollback a sequence? There
> >are certain occasions where your external auditor (unless it's AA)
> >won't accept "That's the way sequences work" as a reason why there
> >are missing numbers in your financial transactions.
> >
> >Philip
> >
>
>---=====================================================================---
> > Philip Chee: Tasek Corporation Berhad, P.O.Box 254, 30908 Ipoh,
MALAYSIA
> > e-mail: philip_at_aleytys.pc.my Voice:+60.5.291.1011 Fax:+60.5.291.9932
> > Guard us from the she-wolf and the wolf, and guard us from the thief,
> > oh Night, and so be good for us to pass.
>
Received on Tue Apr 23 2002 - 17:04:38 CEST