Board index » delphi » Update Generator Value

Quote

I am using Interbase 7.5 and I have a few tables with Field "SID" as
primary key. I am using a generator and a trigger to update the values for
the primary key. The tables are populated with bulk entries automatically
by the user program.

My problem is that occassionally I get a PK violation on the SID values
when the tables are updated. I know that this is happening because the
value of the generator doesnot match the value of the last record in the
table.

If I manually edit the value of the Generator to the value of the last SID
record then I do not get any errors.

Please tell me how I set the value of the generator so that it matches the
value of the last record in the table. I believe that I can use a Stored
Procedure to do this but I do not know how to write the code.
Sounds like the PK is being assigned a value from a source other than the

Quote

"Roy" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>I am using Interbase 7.5 and I have a few tables with Field "SID" as
>primary key. I am using a generator and a trigger to update the values for
>the primary key. The tables are populated with bulk entries automatically
>by the user program.
>
>My problem is that occassionally I get a PK violation on the SID values
>when the tables are updated. I know that this is happening because the
>value of the generator doesnot match the value of the last record in the
>table.
>
>If I manually edit the value of the Generator to the value of the last
>SID record then I do not get any errors.
>
>Please tell me how I set the value of the generator so that it matches
>the value of the last record in the table. I believe that I can use a
>Stored Procedure to do this but I do not know how to write the code.

Sounds like the PK is being assigned a value from a source other than the
generator or the generator is somehow being reset. Generators are
guaranteed to always give a unique value.

Can you show us the source for the trigger that assigns the PK value via
the generator?

Dan
The only source for the trigger and generator is from the metadata as

Quote

Quote

"Dan Palley" <XXXX@XXXXX.COM>writes
news:45b7e940$XXXX@XXXXX.COM...
>"Roy" <XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>>I am using Interbase 7.5 and I have a few tables with Field "SID" as
>>primary key. I am using a generator and a trigger to update the values
>>for the primary key. The tables are populated with bulk entries
>>automatically by the user program.
>>
>>My problem is that occassionally I get a PK violation on the SID values
>>when the tables are updated. I know that this is happening because the
>>value of the generator doesnot match the value of the last record in the
>>table.
>>
>>If I manually edit the value of the Generator to the value of the last
>>SID record then I do not get any errors.
>>
>>Please tell me how I set the value of the generator so that it matches
>>the value of the last record in the table. I believe that I can use a
>>Stored Procedure to do this but I do not know how to write the code.
>
>Sounds like the PK is being assigned a value from a source other than the
>generator or the generator is somehow being reset. Generators are
>guaranteed to always give a unique value.
>
>Can you show us the source for the trigger that assigns the PK value via
>the generator?
>
>Dan

The only source for the trigger and generator is from the metadata as
follows:-
The code for the Trigger is as follows;

CREATE TRIGGER "TRIG_BSSAP_BI" FOR "BSSAP" BEFORE INSERT
AS BEGIN
IF(NEW."SID" IS NULL) THEN NEW."SID" = GEN_ID("GEN_BSSAP_SID",1);
END ^
SET TERM ;^
Ok, so if the record being inserted already has a PK assigned, the generator

Quote

My problem is that occassionally I get a PK violation on the SID
values when the tables are updated. I know that this is happening
because the value of the generator doesnot match the value of the
last record in the table.
If the generator is *always* being used to assign the next PK value, and it

Quote

Please tell me how I set the value of the generator so that it
matches the value of the last record in the table. I believe that I
can use a Stored Procedure to do this but I do not know how to write
the code.
select max(SID) from table into lastvalue;