Good morning. I'm having a big problem.
I'll try to explain the problem.
I am currently migrating a legacy system that has over 2000 tables.
The tables do not have sequence linked to the primary key. When an insertion is made, the application performs a "MAX (id)" and insert triggers. That's too bad, because the generation IDs are linked to the application and not the bank.
Our company, which is responsible for this migration is reshaping the entire database by removing inconsistent data, creating FK's.
The database has two tables, the current table (which is old table) and the new table (remodeled), and these two tables must contain the same data, because we will release the new system modules.
When launching a new module, both the old system as the new system may use a single table.
When recording in the table of old application, the data should also be recorded on the new table.
I'm passing the following problem:
When I do an insert for the current application and at the same time I do an insert in the old application, in the same table, the ID ends up being "lost."
Example:
Old app
Insert table in doc
Before trigger set id = max (id) +1
After insert trigger in new table => id = max (id +1)

Newapp
Insert table in doc
Before trigger set id = max (id) +1
After insert trigger in old table => id = max (id +1)

If the insert is done at the same time, there will be replication ids.
Remember that you can not put a SEQUENCE in the old table, because the moorings of the system are made via the JPA and not internally in the bank.
Has anyone gone through this problem? Do you have any possible solution to share?

I'm assuming that you don't really need consecutive IDs. You could allocate a pool of IDs for one of the tables, e.g. in the new app Just make sure that the number is large enough to accommodate insertions in the old table.

Your idea would work if the data in a table were not synchronized, ie the old record in the table has a trigger that writes the same data in new table.
MAX takes the highest value and adds 1, ie, will give the same problem.