×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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

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

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

Cyprus

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

(OP)
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

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

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 smile

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close