Board index » delphi » Update Generator Value

Update Generator Value


2007-01-25 06:56:41 AM
delphi77
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.
Thanks for your help, it is much appreciated..
Roy
 
 

Re:Update Generator Value

"Roy" <XXXX@XXXXX.COM>writes
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
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
 

Re:Update Generator Value

"Dan Palley" <XXXX@XXXXX.COM>writes
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
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 ;^
and for the generator;
CREATE GENERATOR "GEN_BSSAP_SID";
SET GENERATOR "GEN_BSSAP_SID" TO 0;
SET TERM ^;
Quote

 

Re:Update Generator Value

"Roy" <XXXX@XXXXX.COM>writes
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
isn't called. If the on-insert trigger is the *only* way to assign a
primary key, then have it raise an exception if the PK is not NULL; that
should help you isolate what's causing the rogue PK value.
Dan
 

Re:Update Generator Value

Roy writes:
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
is always being called correctly (with 1 as the second parameter to GEN_ID)
then this should not be possible. Somewhere there must be code that is
either not using the generator or is calling it with 0 as the second
parameter.
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;
newvalue = GenID(genname, lastvalue - GenID(genname, 0));
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
SpaceShipOne; GovernmentZero