I need to increment the default value of a field by 1 based on the largest number already in that column. I will need to be able to update this number at a later time, so obviously this is one reason why I cannot use the built-in auto-increment setting. I am trying to determine the cleanest approach to do this, whether is be a trigger or function or some other idea. I am a newb so your help would be appreciated.
The table "Photos" is basically as follows...
PhotoID - primary key
PhotoName - varchar(20)
SortOrder - (int) - **this is the field that needs to increment. If the highest value is 2500, then the next insert needs to set this field to 2501. Make sense?**
Using MS SQL Server 2005