> > You can reduce the risk of conflict by delaying the sequence
> > creation until the entry is committed - although there is no
> > way of duplicating this at the server trigger level which
> > appeared to be where this question started.
>
> Yes that is my problem, the app under IB will likely be using the
> same DB for more users therefore lies my wanting to process this
> on the server side using triggers. Should I consider writting a
> UDF for this purpose, I do not beleive that UDF are serialized ?.

UDFs will not help for this requirement - other than assisting with
the formatting of the complex field structure (substring functions
etc).

If the only application accessing the database is your own, then you
can probably get away with performing the operation at the client as
you always have. But the timing is wrong. With the field in question
as the primary key you cannot leave it blank and then perform a quick
update in a BeforeCommit handler to set the sequence number. I guess
if the form is very simple (no master/detail stuff) then you can
probably get away with using BeforePost, as long as you commit
immediately afterwards.

You need to consider a separate primary key if you are going to
perform server-side processing on the field in question. A
separate/surrogate primary key would be required so that IBO can keep
track of newly created records using generatorlinks or similar
mechanism.


There are a number of possibilities...

1. Cached updates - can be used to hold onto all the changes, even
master/detail until you are ready to commit all of them. I am not a
fan of Cached Updates, but this is one area where they can be useful.

2. Setup the form(s) so that the user must create the entry (and
commit it) before providing any details - forcing the just the
sequence/identifying record itself to be created. This action can
therefore be very quick and minimise update conflicts. I generally
think of this as a "wizard" style setup, where you prompt the user
through a series of short steps so you can maintain tighter control
over what is happening.

The difficulty is in trying to work out what to do if the user wants
to cancel the creation (or if the application/PC fails in the middle).
This can often be managed with "flag" fields that are only cleared
when successfully completed. A separate process can be used to check
for incomplete items and take the appropriate action.


3. Have the user create the record details first and then assign a
number to the record later - possibly even in a batch process. This
method requires some administrative/process reporting to be put in
place to ensure no records are ever left un-sequenced.


4. Maintain a separate table that you insert sequence numbers into.
When a new record is created, you generate the sequence field value in
the insert trigger (but use a separate/surrogate key for IBO purposes)
and try to insert the new sequence value into the sequence number
table. You catch any exceptions with a "WHEN" statement and retry
with a new sequence number. This processing could be embedded in a
stored procedure executed from the trigger to allow some level of
recursion.

This setup means that your separate sequence number table is entirely
redundant, it is there only to allow you to catch and handle problems
with duplicate sequence numbers inside the insert trigger of your main
table.

The arrangement does mean that it would be possible to fill holes in
the sequence numbers caused by cancelled transactions - if that were
required/appropriate. Such processing would only be practical if the
likely number of sequences is fairly small.


There are probably other scenarios, but these are just a few that come
to mind at the moment.

Geoff Worboys
Telesis Computing