Hi,
sorry, the gen_id .... with 0 was a typo. I'm using a value of 1.
Can't put it into an after insert because of the new.val1 = 0 (the
last statement of the trigger).

regards, Colin

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:16 PM 16/01/2004 +0000, you wrote:
> >I have a requirement to insert a record into a table from that
> >table's "before insert" trigger.
> >
> >The mechanism works, but thereafter my database starts behaving

very

> >strangely. (tried with FB 1.5 RC7 and RC8)
>
> Let me guess - lots of duplicated ids? Or, if the duplication was
> intentional, some id's that you intended to be duplicated but which

were

> not, or duplicated the wrong record's id?
>
>
> >I have read somewhere that it is not recommended to perform any
> >action in a trigger that may cause the trigger to be re-invoked.
> >Does anybody have any ideas?
> >
> >regards, Colin
> >
> >a simplified example follows:
> >(and I really do have a strong argument to want to do this!)
> >
> >CREATE TABLE TABLE1 (
> > ID INTEGER,
> > PRODUCTCODE INTEGER,
> > VAL1 INTEGER,
> > VAL2 INTEGER
> >);
> >
> >
> >CREATE TRIGGER TABLE1_BI0 FOR TABLE1
> >ACTIVE BEFORE INSERT POSITION 0
> >AS
> >begin
> > /* when val1 and val2 both non-zero, rather write 2 records:
> > one with only val1, one with only val2
> > */
> > if ((new.val1 <> 0) and (new.val2 <>0)) then begin
> > insert into table1(id, productcode, val1, val2)
>
> > values (gen_id(table1_gen, 0),
>
> This gets the old value of the generator. However, it's not

necessarily

> the value that you just (presumably) got by calling gen_id() from

the

> client. Generators operate outside of transaction control so any

other

> transaction could have upped the generator since then.
>
> > new.code,
> > new.val1,
> > 0);
> > new.val1 = 0;
> > end;
> >end
>
> Also, put this stuff into an After Insert trigger and, if you want

both

> records to have identical id's then use new.id (the value you have

already

> got).
>
> /heLen