Thank you very much Adam. You were very clear and really solved my problem.

Thank you to Alan too.

Gustavo
----- Mensaje original -----
De: Adam
Para: firebird-support@yahoogroups.com
Enviado: Lunes, 23 de Mayo de 2005 00:08
Asunto: [firebird-support] Re: Knowing the recent value of a generator

--- 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




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]