Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

cyprus106

Programmer
Apr 30, 2001
654
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
 
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
 
Perfect! Thanks jimbob! One question though, what's "<<sql used to generate the keyNumber value>>"?

Cyprus
 
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
 
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 :)
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top