We have multiple tables containing a column having the name "Code", it is an identity column. Our application is running by our multiple locations/clients, and they are using the same database. Due to this simultaneous access of a table, our identity columns are suffering from a gap for each client.
Let's take an example:
Client C1 creates a record and he gets the Code as '1', meanwhile, Client C2 creates a record and he gets the Code as '2' which is wrong. He should gets the Code as '1', this issue is causing because both clients are using the same table.
We can use triggers to maintain custom sequence but in that case, we have to maintain trigger for each table which is tedious alternatively, We can use sequence.
By taking of sequence is there any possibility that we make such a sequence that can take a function and this function can catch the table name internally without passing the table as a parameter.
If there is any best solution that can solve this problem please recommend.