There are really three methods, all are acceptable by may have pros and cons.
First is to store the number somewhere else, for example the other table idea
someone set forth, which is fine and used by many people, but it can result in
delays if the data volume is fairly high, for example a large retail or banking
application. Also you need to maintain these values somewhere. The advantage,
the logic is simple, and the value is known ahead of time, no second "record
number" is required.
Second, use a queue, if a record is rolled back, then the number is stored in a
queue, and inserts always get numbers from the queue if there are any available
and use those before creating new values. The disadvantage here, is that it's
possible for numbers to get out of sequence with inserts, and there may be skipped
numbers at any point in time, but they will eventually get used. The advantage, is
that it doesn't slow down other inserts, as the first method does, and no second key
is needed as the third method requires.
Third, the delayed number, in this case you delay inserting the number, until the
very last point, for example in an after insert trigger. The disadvantage is that you
must maintain a second key value for relationships, that is known ahead of time, in
the case of Invoices for example you would have an invoice number and a record
number, detail records would be linked by the non-audited, invisible record number.