|Subject||Re: [ib-support] Using generators|
> Surrogate keys ids are great for things internally butThe important think with generators in triggers is to ensure that they
> I'm finding them a bit of a pain for import/export type
> functions! Perhaps I just have to give up using SQL for
> import/export and instead write comma delimited files
> and then handle the logic of ids internally...
only fire when no specific identity was provided.
IF (New.ID IS NULL ) THEN
New.ID = GEN_ID( item_gen, 1 );
This gives you two advantages; 1. you can do data transfers without
the key values being regenerated. 2. you can setup procedures that
generate the value internally and provide it explicitly to the
insert - which may be the solution to your other question...
When you create your item record you create the ID in the procedure
and then pass that explicitly to the insert.
DECLARE VARIABLE ITEMID INTEGER;
ITEMID = GEN_ID( item_gen, 1 );
insert into item values (:ITEMID, '1000', 'Test');
insert into item_tax values (:ITEMID, 123);
insert into item_tax values (:ITEMID, 456);
Because you are not passing a null item value the insert trigger (if
implemented as described above) does not reassign the ID but uses the
value given. This means that in the procedure you now know the ID of
the newly created item record and can use it when creating related