--- In firebird-support@yahoogroups.com, "Gustavo" <gusm@d...> wrote:

> Hello:
>
> I use Delphi 5 and IBX.
>
> I have a table TABLE1 with a field CODE1, a generator

TABLE1_GEN_CODE1 and the next trigger:

>
> CREATE TRIGGER TABLE1_SET_CODE1
> FOR TABLE1
> BEFORE INSERT AS
> BEGIN
> NEW.CODE1 = GEN_ID(TABLE1_GEN_CODE1, 1);
> END;
>
> After I make a Post of a new record in TABLE1, I want to know

the value of the field CODE1 that was assigned by the generator but
the field that "identifies" the record is just the field CODE1, so I
can´t make a SELECT... WHERE CODE1=XXX because I don´t know the value
of XXX.

>
> I can make the next statement immediatly after the Post:
>
> SELECT GEN_ID(TABLE1_GEN_CODE1,0) FROM TABLE1
>
>
> and it works. But the question is: Is it possible that immediatly

after my Post and before my SELECT another user makes a Post to
TABLE1 incrementig the generator and then my SELECT obtains a value
of the generator which is already incremented by one more than in my
CODE1? If the answer is yes, then my next question is: How can I know
the number assigned to fiele CODE1 immediatly after the INSERT
statement?

>
> I tried to be clear but I don´t know if this message is clear

enough.

>
> Thanks in advance
>
> Gustavo
>
>
> [Non-text portions of this message have been removed]

Hello Gustavo,

What you have proposed is not the best way to do it. As you have
identified, there is a chance (probably a small chance but
potentially a very bad scenario) that if there are multiple users,
then you can not be sure that the "SELECT GEN_ID(TABLE1_GEN_CODE1,0)
FROM TABLE1" will return the last generator value YOU queried.

To fix this, make 2 changes.

Firstly, the trigger setting the generator is fine as a fallback, but
not as the primary way of doing it. It is sometimes the only way to
maintain backwards compatibility, but it can encourage bad habits, so
I don't have triggers looking up primary keys from a generator, I
expect my developers to do that.

In your case though, change it to

CREATE TRIGGER TABLE1_SET_CODE1
FOR TABLE1
BEFORE INSERT AS
BEGIN
IF (NEW.CODE1 IS NULL) THEN
BEGIN
NEW.CODE1 = GEN_ID(TABLE1_GEN_CODE1, 1);
END
END
^

Now your trigger only grabs a new generator value if no code was
provided.

Before you form your insert statement, use the following query to get
a unique code.

SELECT GEN_ID(TABLE1_GEN_CODE1,1) AS CODE FROM RDB$DATABASE

I am using RDB$DATABASE because it always contains exactly 1 record,
where as table1 will return lots of rows. Store the value "CODE" in a
variable.

Code is now your number, do with it what you want. No-one else will
be able to use it because the generator has already been incremented.

You can simply do something along the lines of

qryInsert.SQL.Text := 'insert into Table1 (code, name) values
(:code, :name)';

qryInsert.ParamByName('Code').AsInteger := Code;
qryInsert.ParamByName('Name').AsString := edit1.Text;
qryInsert.ExecSQL;

your select might look something like this:

qrySelect.SQL.Text := 'select code, name from Table1 where code
= :code;

qrySelect.ParamByName('Code').AsInteger := Code;
qrySelect.Open;

Hope that helps
Adam