Any way to get IB to assign a key # automatically?

[cyprus106]

I'm writing a program that uses IBase and I have a field set aside for a key number, but since so many records get deleted and created so often, it's been a real challenge trying to assing it key numbers myself. Is there any way for IB to assign a new record a key automatically in a designated field?

Thanks much!

Cyprus

 

[unclejimbob]

Yes, create a before insert trigger for the table and use it to populate the key number column.

create trigger setKeyNumber for mytable
active before insert 10
as
begin
new.keyNumberColumn = <<sql used to generate the keyNumber value>>
end
^

ujb

 

[cyprus106]

Perfect! Thanks jimbob! One question though, what's "<<sql used to generate the keyNumber value>>"?

Cyprus

 

[cyprus106]

Alright I just made a generator for incrementing the number, but I've got myself a problem! The following code generates a SQL error -104
Unexpected end of command

CREATE GENERATOR SetKey;

SET GENERATOR SetKey TO 100;

CREATE TRIGGER GAMES FOR CUSTNUM
BEFORE INSERT AS
BEGIN
NEW.CUSTNUM = GEN_ID(SetKey, 1); <<--- here's the problem
END;



Here's the error:

Dynamic SQL Error
SQL error code = -104
Unexpected end of command
Statement: CREATE TRIGGER SETUPNUMBER FOR GAMES
BEFORE INSERT AS
BEGIN
NEW.CUSTNUM = GEN_ID(SETKEY, 1)


I've looked at a good dozen interbase sites and they've all got the same code I do. Mine's just not working! I can NOT figure out why... I'm using IBase 7 by the way

Cyprus

 

[haroldholt]

Sure, you need to terminate the proc, IB usually uses the caret ('^') by default so something like this should work:

CREATE TRIGGER GAMES FOR CUSTNUM
BEFORE INSERT AS
BEGIN
NEW.CUSTNUM = GEN_ID(SetKey, 1);
END
^

You can also set your own terminator if you like using SET TERM.

"what's "<<sql used to generate the keyNumber value>>"? "
I wasn't sure if your key number was a primary key field or generated via some other method so I left my options open by adding a placeholder for you to enter whatever it was you used to generate a number - in this case a generator

 

[haroldholt]

As you can see I have changed my name from unclejimbob to haroldholt to reflect the fact that this will be my last post in this forum due to my new non-InterBase status. Good luck with InterBase.