Hello Nico, Wednesday, December 20, 2017, 4:34:09 AM, you wrote:
> CREATE TABLE ADDRESSES ( > ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > ADDRESSNAME VARCHAR(100) COLLATE UNICODE_CI_AI, .... > Id = 1 / AddressName = Test / ...... > Id = 2 / AddressName = Test2 / ...... > I want to know the next Id number in my table Addresses without an > insert. In my example it will be 3. I want to reserve (without an > insert) Id number 3 because someone else can also ask the next Id > number. In that case it must be id number = 4, because you've got > Number 1 and 2 in the table and number 3 is reserved. IDENTITY is just lexical candy, useful when you have a generated id that fires only on inserts. You can mess around with the start number to some degree, using DDL commands, but otherwise the underlying generator (sequence) cannot be manipulated, as the name of the internal generator for the column is unknown to clients. This is what IDENTITY is about, in fact. For any id's that you want to manipulate the way you describe, create an explicit generator for it, along with a BI trigger for the table to set the conditions for firing the generator. You can use the NEXT VALUE FOR <generator-name> command when you want to capture the next value without firing the trigger. Generators fire outside the transaction context of the request so, once a value is fetched, the generator cannot return the same value again. Of course, you don't want the trigger to fetch another new value and overwrite the one you reserved, so you must make your BI trigger detect whether the INSERT command supplies a value. Helen Borrie
