How to auto generate conditionl non-identity column in MS SQL ?

I have a table with some vehicle information. Theres a unique name for each vehicle that has a multi part convention like this:  NT2763
 The first letter is the city the car lot is on, the second the major lot section, and the number increments based on what city.  Now although vehicles are not added every day the client still wants this number to be auto generated when the go to add a new vehicle to the inventory. How can I automate this. Ive thought of computed columns but this data comes from seperate tables. Is a trigger the best way? Can anyone point me to some examples of this conditionl non-identity column.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

have a number allocation table and maintain the next reference number on that.

set up a stored procedure to allocate and maintain the sequence for you.
bcombeAuthor Commented:
Thanks L - could you elaborate? Im looking to use the current location of the vehicle for the first alpha character and the lot area for the second - were you suggesting that all comes from the sproc and then what - not sure what an allocation table is
Dale FyeCommented:
Generally it is considered bad technique to store the multiple items of information in a single field.  You should be using three fields for this.  With that structure, you could write a function to return the next largest number for a particular city.  something like the following.

Public function fnVehNum(City as String) as long

    fnVenNum = NZ(DMAX("VehNum", "tablename", "VehCity = """ & City & """"), 0) + 1

End Function

To do it with the three pieces of information you have, you might do:

Public functiton fnVehNum(City as String) as long

    Dim strSQL as String
    Dim rs as DAO.Recordset

    strSQL = "SELECT MAX(Val(Mid([VehNum], 3))) as MaxVehNum FROM tableName _
                & "WHERE [VehNum] Like """ & City & "*"
    set rs = currentdb.openrecordset(strSQL, , dbfailonerror)

    if rs.eof then
        fnVehNum = 1
        fnVehNum = rs("MaxVehNum")

    set rs = nothing

End Function
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


I would agree to fyed to separate the city from the number so you can use a autonumbering method on the number. As every city has it's own sequence you can unfortunately not use the identity feature of SQL Server to achieve this.
But you should in no way use a VBA function to create the next number because if two users save a record at the same time it will happen that if they do it at the same time they both will get the same Max number and so they will produce duplicate values. You should apply a unique key over both columns, the city and the number column to be sure that this will never happen (but it produces a "duplicate key error" in the frontend). Moreover this method has the disadvantage that if you add a record with another frontend (for example by directly enter a value in SQL Server Management Studio) no autonumbering happens. And last but not least, it creates an additional round trip to the server at every save in the frontend.
I would recommend to do this in a trigger. Let's say you have the following table structure:

CREATE TABLE [dbo].[tblLNTest](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [City] [varchar](5) NOT NULL,
 [Number] [smallint] NULL,

(An additional unique key should be added like mentioned above.)
Then you can use the following trigger to create an autonumbering:

CREATE TRIGGER dbo.trgLNTestAutonumber_InsteadOfInsert
   ON  dbo.tblLNTest
 WITH tmpNewNumbers AS
      FROM dbo.tblLNTest
     WHERE dbo.tblLNTest.City=I.City)
           ORDER BY     I.City,I.Number)) AS [Row],
    FROM  inserted AS I
 INSERT INTO dbo.tblLNTest (City,Number) SELECT City,[Row] FROM tmpNewNumbers

If you now need to insert a record (or even bulk insert) you only need to provide the City (and whatever columns are needed in your real table) without the number. Simply submit a NULL value for the number.
The temporary table "inserted" which is only available in a trigger contains one or more records which should be inserted at the same time. As this is possible with for example "INSERT INTO...SELECT" the trigger creates an independent row number for any city in the "inserted" table and then adds the current Max value for any city in the data table to the row number.
So the INSERT command in the frontend for this example only needs to be "INSERT INTO dbo.tblLNTest (City) VALUES ('Cityname')". That's all.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:

To be fair, I didn't say when to call the function.  I usually do it in the BeforeUpdate event of the record so that it happens just before the record is written; significantly reducing the chance of duplicates.

bcombeAuthor Commented:
Gents, I am most appreciative - please let me digest and revert - many thanks
Hi fyed,

that doesn't make any difference. I had the same method in a similar database and only two users in two locations were enough to often produce duplicate numbers.

The problem with data which should be unique is that it must be unique any time, not 99% of time...;-)


bcombeAuthor Commented:
Hi folks - Im very soory to have not responded sooner as I had a personal situation - I promise to respond within 24 hours
bcombeAuthor Commented:
"Soory" ,   by the way, is even more sorry than sorry .
bcombeAuthor Commented:
I was able to implement the trigger along the lines suggested by BitSqueezer adding what I needed to accommodate for other detail such as a the lot zone and whether the vehicle was a sub-rental etc etc. It is fast and in testing we were not able to induce duplicate ID's. I am grateful for everyone's input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.