Hi,

Got this from another NG, what is your impression ?

some ideas:
before update / insert trigger
If you need both, they can call a stored procedure.
This code is not transaction-save!

SELECT CAST(MAX(SUBSTR("FIELD",8,10)) AS INTEGER) + 1
FROM "TABLE" WHERE SUBSTR("FIELD",1,7) = SUBSTR(NEW."FIELD",1,7) INTO
:"TEMP_INT";
IF("TEMP_INT" BETWEEN 0 AND 9) THEN "TEMP_CHAR" = '00' ||CAST("TEMP_INT" AS
CHAR(1));
ELSE IF("TEMP_INT" BETWEEN 10 AND 99) THEN "TEMP_CHAR" = '0' ||
CAST("TEMP_INT" AS CHAR(2));
ELSE IF("TEMP_INT" BETWEEN 100 AND 999) THEN "TEMP_CHAR" = CAST("TEMP_INT"
AS CHAR(3));
NEW."FIELD" = SUBSTR(NEW."FIELD",1,7) || "TEMP_CHAR";

hth,
Jan Henrik Sylvester <me@...>


TIA
Marc

----- Original Message -----
From: "Geoff Worboys" <geoff@...>
To: <IBObjects@yahoogroups.com>
Sent: Wednesday, March 14, 2001 9:35 PM
Subject: Re: [IBO] Help on stored procedures/trigger


> > > 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
>
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>