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

Simply add following

ALTER TABLE my_table ADD CONSTRAINT uq_code_name UNIQUE(code, name)

> If I lock the table, it means the program can only work in single user
> mode. In fact, I have never tried the Firebird feature locking table. :)

It is designed for another tasks.

> I have tried to use trigger AFTER INSERT to check whether any record
> has been inserted or not. If any record has been inserted, it will
> throw firebird exception. But it seems that The trigger AFTER INSERT
> can only detect inserted record after the other transaction commit
> first. It means that the problem cannot be solved using trigger AFTER
> INSERT.

That is wrong - trigger works in the same transaction and won't see the
uncommitted records. And the AFTER triggers cannot change the outcome of
the operation as well.

Roman