On 2017-12-20 19:38, 'Zoran' zoran565@... [firebird-support]
wrote:

> 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_CONS TRAINTS 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)?

No, that approach is not legit.

If you are using an identity column, then you should not mess with its
underlying sequence, that is an implementation detail, and therefore
something you shouldn't rely on as it might change from one version to
the next (eg if Firebird developers decide to implement identity columns
differently, or make those 'behind the scenes' sequences inaccessible,
etc). If you want to do something like that, then just create an real
sequence, and use the 'classic' trigger approach.

Given you are talking about master-detail, you should insert the master
record first anyway (or at least, I hope you are using a foreign key
constraint), so use INSERT .. RETURNING <id-column-name> to insert the
master record and obtain the generated value of the id column, then use
that value to insert your detail records.

Mark