Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leahsmart
Posting Yak Master
133 Posts |
 Posted - 2004-01-14 : 04:32:55
|
| Hi Reader,I have a table called tblIncidents and each incident has a reference. When a new incident is created I need to assign it a unique reference. The references should go up in ascending order e.g. 1, 2, 3, 4, 5. Gaps may be created if an incident is delete but that is fine.The only hard part is that a user can edit the reference I have given an incident when I created it. So I cannot use the Identity columns. Also I cannot use a number field as the user may want to enter characters.So basically when a new incident is added I need to find the next unused number to use as the reference.Any help would be great.ThanksLeah |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-01-14 : 05:13:05
|
| My thoughts here are running along the lines of having two different columns. One column is a Identity, not null, always the numerical reference, another is a nullable column that only has a value if the id is changed at a later date. You could either alter the end user apps to deal with displaying the correct one of the two fields, or create a view that shows the reference according to whether the updated reference is not null.Or that could be nonsense. ;)-------Moo. :) |
 |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-01-14 : 05:55:36
|
| How about a lookup table storing one row with the table name and a numeric value? Then write a function you call which is passed the table name and increments the lookup table and returns the new value.I use this approach so you can have 'Next' numbers for more than one table in your database.Raymond |
|
|
|
|
|
|
Generate a unique incremental reference