Adding SQL Trigger to update field on INSERT (multiple rows)

Question

I have an address point Feature Class that has an addressID field. I am trying to write a trigger that will in effect auto increment this field when a new address is added to the map. I cannot alter the table and I cannot rely on users to populate this field when they edit.

So far, I have written a trigger that works great as long as only 1 edit at a time is made to the address feature class.

The working trigger does a count on the number of records already in the feature class (this includes the inserted record) and then updates the addressID field of the newly inserted record with the count.

However, if there are multiple records inserted (as in the user creates several new addresses and then saves edits) all of those records are given the same addressID.

So what I need is a modified trigger that correctly handles multiple row inserts. Problem is I don't really know how to do this and after much searching and reading many posts regarding the same on stack overflow and other forums, I still can't seem to figure it out!

The trigger that works for single row inserts is below...can anyone offer suggestions that may resolve my issue?

 CREATE TRIGGER trgNewAddressPointTestInsert ON [dbo].[AddressPointTest] 
 FOR INSERT
 AS
 UPDATE [dbo].[AddressPointTest] 
 SET addressID = (SELECT COUNT(*) FROM [dbo].[AddressPointTest]) 
 WHERE OBJECTID in (SELECT OBJECTID FROM inserted)

• Peter Horsbøll Møller: Too bad you aren't able to add the Identity type to your AddressID column as that would make SQL Server manage this for you
• cdd: I agree, that would be ideal. Unfortunately the table owner was not willing to go that route.

Answer 1

inserted Table is a temporary rowset which may contains more than 1 row and this DML query simply assign one value to all records which are in it.maybe this lines help to make up this problem.

Declare @RowCount as int
Select @RowCount=Count(*) From [dbo].[AddressPointTest]
Select ObJECTID,ROW_NUMBER() over(Order by OBJECTID) AS aID into #T1 From inserted
Update #T1 Set aID=aID+@RowCount
Update [dbo].[AddressPointTest] Set addressID=#T1.aID From [dbo].[AddressPointTest] A inner join 
 #T1 On A.OBJECTID=#T1.OBJECTID

these lines create a temp table(#T1) with 2 columns,OBJECTID and aID.first aID values are simple 1,2,...n where n is row count in inserted table.then update this field with proper value and finally update main table with proper values by joining it to #T1 using OBJECTID with this in mind that OBJECTID is unique in that table. Simply replace that update expression with this block of code.

• cdd: That works great! I would vote you up but I don't have the reputation. I know I'm not supposed to say thanks here but THANKS! I would have never thought of the temp table approach.