January 11, 2007 at 10:01 am
Is there a way that I can declare a default value that takes the greatest number in a given field, OrderID, and increment it by one, thus giving me a default value for a newly inserted row? What I have is a data table that contains a bunch of links that I am displaying in a web page, and the user needs to have the ability to reorder them. That part is no problem. I was just wondering if there is a way that I could set a default value for a newly inserted link. For example, if the highest orderID is 10, then a newly inserted record would automatically default the orderID to 11.
January 12, 2007 at 8:40 am
Am I correctly deducing that you're changing the Order ID field? If so, IDENTITY won't work for you. And is Order ID part of the primary key? Are you changing the primary key value when the user re-orders links?
You can use "select @val = max(id) + 1" however that might not work in a multi-user situation, especially for key values.
Imagine this scenario:
User A begins a transaction
A: SELECT @val = MAX(id) + 1 FROM OrderTable
A: -- @val = 11
User B begins a trasaction
A: INSERT INTO OrderTable VALUES (@val, ....)
B: SELECT @val = MAX(id) + 1 FROM OrderTable
B: -- @val = 11 (A's transaction isn't committed yet)
A: COMMIT
B: INSERT INTO OrderTable VALUES (@val, ...)
B: Primary key error!!
One solution: use IDENTITY for the Order ID field. Then add a non-unique column for ordering. Sort by this column with the Order ID as a secondary sort field. Even if you get duplicate values, the ordering should still be as expected.
There are other ways to solve this problem, but it really depends on the complete scenario of what you're trying to do.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply