Hi,

> Created Generator:
> <------------------
> CREATE GENERATOR GEN_TABLE1_ID;
> ------------------>
>
>
> I have created TABLE and IDFIELD (integer, PrimaryKey), FIELD2 in the

table:

> <--------------------------
> CREATE TABLE TABLE1 (IDFIELD INTEGER NOT NULL, FIELD2 SMALLINT);
> ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (IDFIELD);
> --------------------------->
>
> note: These are auto generated codes by IBExpertPE. I didn't write them
> manually. I WISH I COULD.

First of all, there's no NEED to use the "auto-inc" facility in IBExpert.
You
can write those triggers by yourself if you like.

> Created trigger:
> <-------------------------
> CREATE TRIGGER TABLE1_BI FOR TABLE1
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF (NEW.IDFIELD IS NULL) THEN
> NEW.IDFIELD = GEN_ID(GEN_TABLE1_ID,1);
> END
> ---------------------------->
>
> When I try to insert a record and return the ID
> This statement returns an error:
> <--------------------------
> insert into TABLE1 (FIELD2) VALUES (44)
> select GEN_ID(GEN_TABLE1_ID,1) from rdb$database;
> -------------------------->

Who told you to do that? No-one did. Listen:

You cannot generate a new value (in the trigger) and return that value
to the client in a single statement!

Get it?

> There is no problem if I don't try to return GEN_ID(GEN_TABLE1_ID,1).
>
> What is GEN_ID(GEN_TABLE1_ID,1)?
> What "GEN_ID", "GEN_TABLE1_ID" and "1" stands for?
> Can you recommend some readings for this subject? So I don't keep the
> maillist busy with me.

GEN_ID is a built in function "generator id" to get a value from a
generator.

"gen_table1_id" is the name of the generator.

"1" means "increase the value of the generator by 1 when executing gen_id".


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com