How do I make an field that is autoincremental in Interbase?

[Guest_imported]

How do I make an field that is autoincremental in Interbase?

 

[svanels]

If you want to use the auto increment field to generate key fields, this can help.

Suppose you have a recID number which must be unique, define a trigger, coupled to a number generator.

Suppose clientID is a integer number in the table CLIENTS

Use WISQL or other tool to key in the following code:

create generator client_number_gen;

create trigger CLIENTinsert for CLIENTS
before insert
position 0
as begin
NEW.CLIENTID = gen_id(client_number_gen,1);
end


Every time a new record is inserted, interbase will take care of generating the number.

To start 251, 252 etc:

set generator client_number_gen to 250;


regards
S. van Els
SAvanEls@cq-link.sr

 

[Guest_imported]

Special Thanks))

 

[Pamal]

I would like to suggest one change to svanels suggestion, just in case ID already has been give a value by the client app

create trigger CLIENTinsert for CLIENTS
before insert position 0
as begin
IF (NEW.CLIENTID is null)
then NEW.CLIENTID = gen_id(client_number_gen,1);
end

 

[svanels]

If it is an insert then New.clientId always is null. If ClientId is defined as a key field Interbase will take care of duplicates. However if you construct the trigger for an existing table, be sure to check what your highest number is.

My opinion is that numbering should be handled by the Database, and not by the client applications. This to avoid inconsistencies and data corruption.

Regards

S. van Els
SAvanEls@cq-link.sr

 

[delphiman]

Why am I so unlucky?

I have CAKV an integer number in table CHILD

From the above I have

create generator cld_number_gen;

create trigger CLDinsert for CHILD
before insert
position 0
as begin
NEW.CLDCAKV = gen_id(cld_number_gen,1);
end

But if I run it I get the following exception ....

Dynamic SQL Error
SQL error code = -104
Unexpected end of command
Statement: create trigger CLDinsert for CHILD
before insert
position 0
as begin
NEW.CLDCAKV = gen_id(cld_number_gen,1)

What am I missing?

TERRY

 

[Oppenhiemer]

Try defining a term so that Interbase can parse the SQL properly. Such as..

SET TERM !! ;
create trigger CLDinsert for CHILD
before insert
position 0
as
begin
NEW.CLDCAKV = gen_id(cld_number_gen,1);
end!!

That should work.

Opp.

 

[svanels]

That is it Opp!, I am accostumed to run this things from the command line, that is why I didn't use a terminator. Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Actually not

But thanks guys! The following did ...

SET TERM !! ;

create trigger CAKVinsert for CHILD
before insert
position 0
as
begin
NEW.CAKV = gen_id(cld_number_gen,1);
end!!

But I remain puzzled to find that when I load records (via my IBConsole) the
CAKV did not acquire a (auto-incrementing) number - as happens with an AutoIncrement
field in a Paradox Table - as I expected it would.

What am I still missing?

 

[svanels]

I found an nteresying link on the subject

http://delphi.about.com/gi/dynamic/.../www.scalabium.com/articles/id_generation.htm

Regards Steven van Els
SAvanEls@cq-link.sr

 

[Oppenhiemer]

Yes - very interesting svanels. This is a much debated area, and something that most programmers have to confront at some point.

I think the best you can say - is that there is no one - ideal method; and whatever stratergy is used - must depend on the context of its implementation.

Regards..

Opp.

 

[delphiman]

Hi Steven!

My problem as described in May 20th still persists.

Did you ever have the method you describe on 14thMay 2001 in this thread to actually work?

In this thread (on May 23) you mentioned ....

>I found an interesying link on the subject

http://delphi.about.com/gi/dynamic/.../www.scalabium.com/articles/id_generation.htm

I didn't find anything there of use to me with this
problem.

Any more ideas?

 

[svanels]

Yes it works, but if you have an existing table with already assigned numbers, make sure that your generator starts higher then the last number in the table, are you will get key field errors.
You have define the generator first, after that the trigger Steven van Els
SAvanEls@cq-link.sr