Unanswered: Sequencial, Unique Number tied to header, Locking Q?
I have a situation where for a given customer, their invoices need to be sequentially numbered, without gaps.
Customer A Invoice 1,2,3,4 ...
The issue is 2 people creating an invoice for the same customer at the same time. Currently I don't assign the Invoice number until the user hits 'Save'. At that time I query for max(invoiceno) against the customer key and simply add 1. it's the last operation prior to saving against the backend.
If 2 users hit Save at the exact time, I'm getting the same (duplicated) Invoice Number.
What suggestions/techniques do you have to resolve this? Would "locking" of the customer record and storing the last highest invoice there play a part?
Put a unique constraint on the combination of CustomerID and InvoiceNumber.
If two users actually save records at the exact same time, one of the records will error out, at which time your interface can resubmit it for a new invoice number.
If it's not practically useful, then it's practically useless.
May I ask if the sequential numbers without gaps requirement still applies if one of the records gets deleted? If so should this not be derived at runtime (or alternatively accounted for in a trigger - not my preference I might add)?
If not then presumably this number needs to be derived by code rather than inputted by the user (in case the user enters an out-of-sequence number)?
Just throwing in a couple of the issues that typically arise with this sort of thing
1. For this app, deletes are not allowed. A "Void" flag approach is used.
2. If there was a gap - that is actually acceptable, just not preferred. The hard requirement is that there aren't any duplicate customer+invoice numbers. The "no gap" made my explaining above easier, but isn't a hard requirement.
3. The app requires VCR buttons on the record to find the next highest record. Even though we shouldn't see gaps, I coded it within the app so it can handle a gap of up to 10 records missing.