Stephen,

I think this might have been a bit of a wild goose chase. The problem
may not be a bug or corruption after all, but rather transaction
isolation.

>
> 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

What does exists do? If there exists a record matching the statement
inside **** that is visible to your transaction ****, then it returns
true.

Now consider this

Tr A starts,
Tr B starts
Tr A does the exists check and it returns false so an insert is made
Tr B does the same exists check, but because Tr A is not committed, it
returns false, and Tr B ALSO does the insert.
Tr A commits
Tr B commits

Even if TrA committed before TrB does anything, unless TrB is read
committed, it will not see the record.

If you need to define a field as unique, then add it as a constraint
on the table. Otherwise, your system is not safe for simultaneous
transactions. Even if your transactions are read committed, I have
included a case where it is possible to create duplicates.

Adam

> 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.
>