Subject Re: [ib-support] view and generator
Author Helen Borrie
At 11:54 AM 24-06-02 +0000, you wrote:
>I have two tables and a view based on them.
>table 1
>id1 integer
>text1 char(30)
>type integer
>table 2
>id2 integer
>code_tbl1 integer
>text2 char(10)
>select text1, text2
>from table1,table2
>where type=1 and code_tbl1=id1
>trigger before insert on table1
>id1 = gen_id(gen1,1);
>trigger before insert on table2
>id2 = gen_id(gen2,1);
>the view alow to see informations about elements of table1 wich are
>of the type1 (for this type there is only 1 dependant line in the
>a trigger on the view allow to insert data and put it in table 1 and
>but how to recover the value of id1 after the insertion in the table1
>to put it in code_tbl1 in the table2.
>at this time I use a variable :TEMP=GEN_ID(GEN1,0);
>after the insertion in the trigger of the view, but I'm not sure to
>recover the good value if another insertion in the table1 occured at
>the same time.

This is not multi-user safe, since you have no way of knowing whether it
was your client which took that number or another client.

If you want to retrieve the new id back to your client, you must generate
the number BEFORE you perform the insert; and you should alter your
triggers to be BEFORE insert and make them
if (new.id1 is null) then
new.id1 = gen_id (gen1, 1);

Your client app can get the new generator number using a query like

select gen_id(gen1, 1) from rdb$database

Some client development tools provide functions that do this for you.

Generator-number generation is outside of transaction control; and, once
generated, a number cannot be generated again (unless you do something
stoopid like "SET GENERATOR gen1 TO 1").


All for Open and Open for All
Firebird Open SQL Database · ·