>Not sure this would work in multi-user environment when two users try to create a new order in the same time.

You have to provide the necessary table lock when getting the next order number to use. This lock is only applied for a very short time (was not included in the above code -- it was typed off the "top-of-my-head"). The retry for another user will then be able to lock the table and get the next one. I have used this concept in a database with over a thousand users without incident.