At the customer we are facing the following issue:
We have a table with a primary key (PK), and this field is of course mandatory.
This column is not an identity column, and currently it cannot be made one because we need to remain compatible with the existing system (RPG/iSeries DB2).
Because the field is mandatory, we populate the PK with a number via the DEFAULT, so that it can be sent to the database already filled in. However, it is possible that a second user generates the same number in the DEFAULT, since it is simply calculated as max number + 1.
Duplicate numbers are not allowed, so in the TRIGGER the number is recalculated to ensure it is unique regardless. This uses the same logic as in the DEFAULT.
As a result, when saving, you may receive a different number than what you initially entered. The GUI is then unable to find the inserted row and shows an error message.
How can we solve this without using an identity column?
There is a risk that, for example when adding a sales order, a user believes they are working on their own order, while in fact they are adding order lines to someone else’s order.