s_lutti wrote:

>
> I have a table BAS_USERS with all users in it. The primary
> key is a field called ID. Then I have a second table
> MOV_CREDITS with a field USERID. This field has a foreignkey
> constraint to BAS_USER.ID. My problem comes in, when two or
> more users are working with the same database. User A has
> opened a record from BAS_USERS with the ID = 4. The dataset is in
> dsEdit cause he's editing the lastname for example. Now when
> user B tries to post a new record to MOV_CREDITS with
> MOV_CREDITS.USERID = 4 I get the typical
> "lock conflict on no wait transaction".
> I know that the dbms needs to do so, cause user A could
> change the value for BAS_USERS.ID at the same time user
> B tries to store the old value to the foreign key field.
>

Another approach that looks much more radical than it is
would be to split BAS_USERS into two tables - BAS_USERS_ID
and BAS_USERS_DATA, for example. Put just the ID in the
first table and all the volatile data in the second. Change
the foreign key constraint so it references BAS_USERS_ID.
Create a view called BAS_USERS that joins BAS_USERS_ID
and BAS_USERS_DATA. Create insert, update, and delete
triggers for the view that change the underlying tables.

Your application should not notice any difference except
that you'll only get a conflict message if you actually
change the ID field.

Best regards,


Ann