At 09:13 PM 5/07/2007, you wrote:

>Hi friends, I have java codes to do single task, that is to insert a
>record into table A which
>fields name are id, code, name.
>the field Id is set by firebird through trigger so that the id will
>always be unique.
>
>
>When the java code is run, it will insert table A with
>value 1,'CODE1','NAME'
>
>This code has no problem when the program is run by single user.
>But if 3 users trying to run the code (from different computers), the
>result is
>three records are inserted. They are
>
> 1,'CODE1','NAME'
> 2,'CODE1','NAME'
> 3,'CODE1','NAME'
>
>How to make the two other users fail inserting the record while the
>first time user inserting the record successful ?

This is not a Java problem at all. In fact, the behaviour you have
described is the desired, expected behaviour: three different users
insert three *different* records, because you have a Before Insert
trigger like:

create trigger bi_A for A
active Before Insert
as
begin
new.id = gen_id(myGenerator, 1);
end

Now, if you changed your trigger to this:

create trigger bi_A for A
active Before Insert
as
begin
if (new.id is null) then
new.id = gen_id(myGenerator, 1);
end

...then, the first user's insert using your SQL statement would
succeed and the other two would except with primary key violations.

The problem with your design is "keeping a dog and barking
yourself". It is a *wise* idea to have a primary key generated from
a sequence: it guarantees a unique key every time. It is an
*unwise* idea to then override the generator, by passing a constant
for the key's value.

How do you get your insert SQL statement to pass null? Simply omit
it from the column list, i.e. change your SQL into:

insert into A (code, name
values (?, ?)

Those questionmarks are replaceable parameters so, in case you don't
know how to work with them yet (which *is* a Java matter), let's
simplify it to your statement of constant arguments:

insert into A (code, name
values ('CODE1', 'NAME')

The server will generate the value for id for you, from the sequence
and your three users would all succeed in posting a record - the
scenario you apparently don't want.

So - the real problem you have to address is that you want the column
named code to be unique. Here's how you do that:

alter table A
add constraint uq_A UNIQUE (code)

(and don't forget to commit this DDL before you try to rely on it!)

Now, your statement
insert into A (code, name
values ('CODE1', 'NAME')

will succeed for the first user and will except for the others.

Helen