Hi ALL,
I have a column on a table that is unique and it is being incremented by a trigger each time a new row is added to it. I was doing some testing and added some new rows to this table. Now I am unable to get the sequence right again. When I delete the row that has the highest number sequence and add a new row, the new row gets the next number sequence of the row that was deleted instead of the number of the row that was deleted.
I just noticed that the problem I am having is not an index nor is it a sequence problem. Aparently there is a trigger that does the incrementing of the column based on the maximum value on the column and adding 1 to it. How can I get this column in some kind of a sequence if I do a manual delete and add???
I'm not sure I'm following the question, but I do have a comment on the approach. The problem with a trigger-based incrementation strategy is that it is not multi-user capable. For example, I insert a record into the table. The trigger does a query to get the highest ID in the table, which may be 5. It adds 1 and inserts my record with an ID of 6.
*At the same time*, another user inserts a record into the table, the same trigger does the same SELECT. Since I have not yet commited my INSERT, this second user does not yet see my record and therefore also has the trigger return a value of 6. This is a bad thing. This is why sequences are the preferred solution to this problem.
Reply With Quote
Bookmarks