Problem solve Get help with specific problems with your technologies, process and projects.

Incrementing record id without using identity

The question is related to SQL Server 2000. I need to create an insert trigger where every time there is an insert on the table, I want the recordid column to go to the old value plus one. My trigger looks something like this, but it's not working properly! I can do this similar thing in Oracle, but not sure about this in SQL Server. Also, I know we can use the identity to do something similar, but my requirement is that I cannot use identity. My sample script looks like this:
 CREATE TRIGGER TRGname ON table_name FOR INSERT AS declare @id int select @id = max(recordid) from table_name update table_name set recordid = @id + 1
Any input will help!

I don't understand the requirement to NOT use an identity. Based on that requirement, my assumption is that you are taking and internal database design element, an ID, and giving it a physical meaning. This is something that should never be done as it simply backs you into a corner and can create severe problems if you ever need to move that database to another server or reload the data.

Your code will set that recordid value to the same number for every row in the table. This gets to the second major problem you have. You create database structures such as ID values and attach them to user data in order to provide a unique way to reference a particular piece of data within a table in order to perform joins and make data modifications. In order to make this update work correctly, you would have to grab the max ID value from the table and then update the ID value to the max you just grabbed only for the row that was just inserted. You would have to be able to uniquely identify that row within the table based on what exists in the inserted table. Only then would you have something that could be used to correctly identify a unique row. It's the classic chicken or egg problem. You need the recordid to uniquely identify a row and find it, however you need to find the particular row within the table in order to assign it a recordid.

Now that being said, you have an even more severe problem that you can NOT work around with a method such as this. If 100 different users each insert 1 row of data into that table at the same time, you have 100 concurrent inserts that occur. You are grabbing the max(recordid). This means that more than 1 insert can grab the same recordid and then you are back into the same situation.

It is because of these types of unsolvable problems that I would make the following recommendations:

  1. Eliminate the requirement that you can not use an identity
  2. Implement an identity behind your recordid column
  3. Eliminate the situation where the database structure of an ID takes on a physical or business meaning


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.