Sql-server – error when inserting into table having instead of trigger from entity data framework

entity-framework-4sql serversql-server-2008triggers

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?

Best Answer

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();
Related Topic