Hello Martijn

Hello Olaf,

> I need the last ID from a table. Now I can get this with select max(id)
> from
> table into :maxlnr. I thought, it is a better way to read the last
> generator-id with maxlnr = gen_id(gen_t_dt_id,0); This results a not
> indexed
> read of my table.
>
>
>
> Now I have read the following in firebird faqs: Unless you know very well
> what you are doing, using GEN_ID() with step values lower than 1 may
> compromise your data's integrity.
>
>
>
> What is the best way to get the last value?

Generators are incremented -outside- transaction control. That means if
two transactions insert a new item into your table, the GEN_ID(..., 1)
function will fire twice and your generator is incremented twice. This also
means that if you have to increment and READ the value of the generator,
it might already have been incremented by the second transaction between
your first insert and your read to get the current value.

The best way of doing things depends on your Firebird version, for example,
the following works in all Firebird versions:

Method 1
-----
In your client application, use:

select gen_id(mygenerator, 1) as new_id from rdb$database

insert into ... values (:thenewid, ...)
insert into childtable values (:thenewparentid)

etc...

So, basically: get the value yourself and use it for new data.

If you like to avoid it sometimes because you don't need the new value,
create a trigger that says:

IF ( (NEW.IDCOLUMN IS NULL) OR (NEW.IDCOLUMN = 0) )
THEN NEW.IDCOLUMN = GEN_ID(mygenerator, 1);

Method 2
-----
New versions of Firebird support INSERT INTO ... RETURNING.

So, keep the trigger and use:

INSERT INTO ... VALUES (all_other_columns_except_id_column)
RETURNING IDCOLUMN INTO :mynewid

Here, you get the newly created ID value from IDCOLUMN into client
side parameter "mynewid".

Hope this helps.

With regards,

Martijn Tonies
Upscene Productions
<http://www.upscene.com> http://www.upscene.com

my description was not good.

I need the value in a stored procedure, not by inserting the data! By
inserting I have a trigger. new.id = gen_id(generator,1);

I have a table with some records. Now I need the last 10 inserted records
with an offset of 5 for example. The last ID is 100, I want to get the
records (IDs) 95 to 86 in this case. If the offset is 10, I need the records
90 to 81 and so on.

To get the last ID I thought it is the best way to use the generator with
the value 0, but the description warns about errors.



Thanks in advance

Olaf





[Non-text portions of this message have been removed]