On Thu, Feb 4, 2010 at 17:32, First L <lfeliz@...> wrote:

> Normally I develop an app for a single user and manage serial #'s (orders, invoices etc..) using a Sequence (aka Generator).  I am rewriting an app for the Web and will have one database for all data. My primary key is now  sysClientID, Recid for every table to allow users to totally customize their account.  At issue is, do I declare a set of generators for each client for the visible serial #'s (OrderID, Invoice, JobID etc..) or is there a better way to assign the numbers.
>
> Each subscriber will on average have 1 to 5 users using the app and I expect a range of 150 to 400 subscribers once build out is complete.  Is this a good use for a stored procedure/function?  Does anyone have any examples of this?
>
> One requirement I do have is that I need keep a log of each serial # assigned, as it has been requested by auditors in the past to make sure every invoice is accounted for. I do this in my code already.

Hi,

short answer: don't use generators, as this will produce holes in your
sequence should a transaction get rolled back. Generator values never
get rolled back so your sequence will miss one number.
Long answer: Have a look at the article about "An Auditable Series of Numbers":
http://ibobjects.com/TechInfo.html#ti_AuditableSeries

HTH,
Markus