I have a table ( invoiceTypes) that stores various invoice types and for each type its next available number (integer)
When I enter a new invoice ( in a table invoices) and press save I start a transaction in which I want to get the next available number from invoiceTypes table for the type of invoice I entered. I want to be sure that no other user gets the same number for this invoice type (since (invoice type and number) is a unique key for invoices table). After I commit my transaction other users can access the invoiceTypes table.
I would be grateful if you could propose me a solution to the above problem.
Thank you
As Steve already mentioned, this sounds like the exact situation that a SEQUENCE would be used for. You can create a different SEQUENCE for each type of Invoice that you need to track:
https://docs.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers