I accomplish the following (I no if it will be OK )

SET TERM ^ ;
CREATE PROCEDURE P_NUM_REC (
VCH_ENTIDAD VARCHAR(7),
SIN_ALMACEN SMALLINT )
RETURNS (
INT_NREC INTEGER )
AS
BEGIN
/* Update the number of the reception */
select a.no_r from vtaconteo a where ((a.entidad = :vch_entidad) and (a.almacen = :sin_almacen)) into :int_nrec;
update vtaconteo set no_r = no_r + 1 where ((vtaconteo.entidad = :vch_entidad) and (vtaconteo.almacen = :sin_almacen));
suspend;
END^
SET TERM ; ^

Regards
=========
|| ISMAEL ||
=========

----- Original Message -----
From: Woody
To: firebird-support@yahoogroups.com
Sent: Friday, August 05, 2011 10:31 AM
Subject: Re: [firebird-support] Duplicate entries ...



From: "Lester Caine" <lester@...>

> I had a bit of trouble on a site yesterday which I'm not sure I understand
> ...
>
> The raw data is basically two tables, one with an entry using a generator
> value
> key, and the second 'detail table' has a primary key of the same generator
> value
> + a transact_no. The detail table is populated by a trigger on the master
> table,
> when one field changes, the current state is stored in detail, and the
> master
> entries updated. The trigger is just using a MAX+1 for the transact_no,
> and has
> the primary key unique index, but in theory a user will be take several
> seconds
> between each action on an individual ticket, and the times confirm that.
>

Generally, the second one to post and commit should get an error if the
primary key is duplicated. However, barring that, when I need something like
this I write a specific routine to insert a new record in an isolated
transaction so it can be posted and committed right away. It would then
return the transact_no, in your case, to the calling routine which would
just position itself to the new item. If an error occurs, it could return a
negative error code indicating what caused it or a generic -1 to indicate
failure.

I do the same thing on a larger scale for table ID's where they need to be
in sequence. I usually create an ID table with 2 fields (ID_Type, Next_ID).
I use a separate transaction to read and set the next ID for whatever I
need. Mainly this is needed when an ID is required when creating a new
record for display, like a new work order, as opposed to an underlying,
non-useful ID where a generator can be used.

I think you've been lucky up until now not running into any problems with
the way you're doing it now. I have dealt with people who leave for lunch or
even for the day without closing a form and saving information right away so
I try to make it as non-interfering as possible so it doesn't cause others
to have problems. It doesn't always work but it gets close. :-)

HTH
Woody





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