That's a good idea. Do you think the code below is safe?

set term !! ;

create or alter procedure sp_test
as
declare variable v integer;
begin
while (gen_id(mygen, 0) <> 0) do
begin
end
v = gen_id(mygen, 1);

-- my stuff here

v = gen_id(mygen, -1);
end !!

set term ; !!

On 11/28/06, Martijn Tonies <m.tonies@...> wrote:
>
> Hi,
>
> > I have an application that process thousands of material transactions
> per
> day.
> > Today, i use some triggers in the transactions table to keep summary
> > tables updated, but these triggers are slowing down my application.
> > These summary tables are not used often, so there is no need to
> > process them immediately, so i'm planning to use another approach:
> >
> > To create a stored procedure to access these summary tables, and when
> > the procedure is called, it will process all pending transactions that
> > was not processed yet, updating the summary tables and returning the
> > result i need.
> >
> > My question is how can i guarantee that when two or more clients call
> > the procedure at the same time, just the first one can update the
> > summary tables, while the other clients waits the end of the process?
>
> Well, the easy way would be to let your procedure somehow know
> that it's running. For example, by incrementing a generator and
> decrementing
> it when it's done.
>
> When the second application calls the procedure, check the generator value
> and if it's 1 (or whatever, but 0), then you exit the procedure.
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>


[Non-text portions of this message have been removed]