> --- In firebird-support@yahoogroups.com, "Ann W. Harrison"
> <aharrison@i...> wrote:
> > Yes, I think so, thought given it's a 1.03 problem and people are very
> > focused on 2.0 and Vulcan, it may not get the attention it deserves.
> >
> > Does your application normally generate duplicates on the primary key
> > and expect the database to reject them? That's OK - I'm just
> trying to
> > imagine how the problem occurred.
>
> These records are created / updated with the following bit of code in
> a stored procedure.
>
> IF (EXISTS (SELECT LDSI_STOP_ID, LDSI_ITEM_NUMBER
> FROM LD_STOP_ITEM
> WHERE LDSI_STOP_ID=:LDSI_STOP_ID AND
> LDSI_ITEM_NUMBER=:LDSI_ITEM_NUMBER
> PLAN (LD_STOP_ITEM INDEX(RDB$PRIMARY40)))) THEN
> BEGIN
> UPDATE LD_STOP_ITEM
> SET LDSI_PIECES=:LDSI_PIECES, LDSI_WEIGHT=:LDSI_WEIGHT,
> LDSI_METRIC=:LDSI_METRIC, LDSI_DESC=:LDSI_DESC,
> LDSI_RATE=:LDSI_RATE, LDSI_AMOUNT=:LDSI_AMOUNT,
> LDSI_KEYWORD=:LDSI_KEYWORD
> WHERE LDSI_STOP_ID=:LDSI_STOP_ID AND
> LDSI_ITEM_NUMBER=:LDSI_ITEM_NUMBER;
> END ELSE
> BEGIN
> INSERT INTO LD_STOP_ITEM
> (LDSI_STOP_ID, LDSI_ITEM_NUMBER, LDSI_PIECES, LDSI_WEIGHT,
> LDSI_METRIC, LDSI_DESC, LDSI_RATE, LDSI_AMOUNT,
> LDSI_KEYWORD)
> VALUES (:LDSI_STOP_ID, :LDSI_ITEM_NUMBER, :LDSI_PIECES,
> :LDSI_WEIGHT,
> :LDSI_METRIC, :LDSI_DESC, :LDSI_RATE,
> :LDSI_AMOUNT,
> :LDSI_KEYWORD);
> END
>
> where LDSI_STOP_ID is a generator derived primary key from another
> table and LDSI_STOP_ITEM is a sequentially assigned item number that
> starts at 1 for each LDSI_STOP_ID.
>
> This is probably sub-optimal SQL but I would still expect it to fail
> if somone created a duplicate record between execution of the IF
> EXISTS and the INSERT.

if LDSI_STOP_ID is a generator value PK, how do you get multiple
LDSI_STOP_IDs such that you can have sequential LDSI_STOP_ITEM values which
start from 1?
Alan