error when inserting into table having instead of trigger from entity data framework

Question

I'm using entity framework 4 , on inserting a new record using entity framework in a table that have instead of insert trigger while the table has an identity column , the instead of trigger is used to modify one of the inserted value according to certain logic ,Entity framework raises exception "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries".

Can any one help how to get around this exception?

Answer 1

Instead of trigger is executed instead of Insert operation created by Entity framework. This can be potential problem because once you are using identity column each insert is followed by:

select [Id] from [dbo].[TableXXX] where @@ROWCOUNT > 0 and [Id] = scope_identity() 

So the question is what happens with this query once insert is replaced. If it is executed and return null you get and exception. You can add it after you insert record in your trigger but it will not help if the original query is executed as well.

You can change your trigger to be either before or after insert and modify data.

Answer 2

Using Entity Framework 4.1, the solution posted by Ladislav to add a Select of Scope_Identity() to the end of the trigger body solved the problem for me. I have copied the entire trigger creation here for completeness. With this trigger defenition I was able to add rows to the table using context.SaveChanges().

ALTER TRIGGER [dbo].[CalcGeoLoc]    ON  [dbo].[Address]    INSTEAD OF INSERT AS  BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT OFF;  -- Insert statements for trigger here INSERT INTO Address (Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, GeoLoc, Name) SELECT Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, geography::Point(Latitude, Longitude, 4326), Name  FROM Inserted;  select AddressId from [dbo].Address where @@ROWCOUNT > 0 and AddressId = scope_identity(); END 

Edit for handling computed values (Thanks to Chris Morgan in the comments):

If you have any other computed values in the table you will have to include them in the SELECT as well. For example if you had a CreatedDate column that uses GETDATE() you would make the select like this:

SELECT [AddressId], [CreatedDate] from [dbo].Addresses where @@ROWCOUNT > 0 and AddressId = scope_identity();