Hello Helen
I understand that we shouldn't mess up with IDENTITY column, as you said. I
have the same problem as original poster and I'm using this code
SELECT G.RDB$GENERATOR_NAME
FROM RDB$RELATION_FIELDS AS G
WHERE G.RDB$RELATION_NAME = 'CUSTOMER'
AND G.RDB$FIELD_NAME = (SELECT F.RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS AS F
LEFT JOIN RDB$RELATION_CONSTRAINTS AS RC ON
(RC.RDB$INDEX_NAME = F.RDB$INDEX_NAME)
WHERE RC.RDB$RELATION_NAME = 'CUSTOMER'
AND RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY')
to obtain IDENTITY sequencer name. Then I increase it to get the next
IDENTITY for master table, which I then populate into master-key columns of
the detail table.
Is this approach 'legit'? Will I run into problems in the future (assuming
that RDB$ table structures don't change)?
Thanks.
Zoran
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, December 19, 2017 2:29 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Info
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
_._,___
