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 + 1Any 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:
- Eliminate the requirement that you can not use an identity
- Implement an identity behind your recordid column
- Eliminate the situation where the database structure of an ID takes on a physical or business meaning
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.