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