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

sql servertrigger

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)

Best Answer

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.

Related Question