29

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?

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
SKumar
  • 1,227
  • 6
  • 18
  • 29
  • 1
    is there a reason you haven't marked an answer correct? Ryan Gross's answer below fixed this exact same issue for me. – Tyler Jones Dec 01 '15 at 16:54

4 Answers4

60

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();
Ryan Gross
  • 6,423
  • 2
  • 32
  • 44
  • 3
    I added "SELECT FROM INSERTED;" to the end of trigger body and it did the trick. – muruge Jun 20 '11 at 20:26
  • 7
    You are amazing. I would vote this up 150,000 times if I could. – Jeff Dec 02 '11 at 17:01
  • 4
    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();` – Chris Morgan Feb 08 '14 at 04:02
  • Is there any way how to do it in MariaDb? It cannot create trigger INSTEAD OF INSERT. – wolen Sep 15 '15 at 19:05
  • 1
    This answer solved my issue, and it should be marked as correct. – Tyler Jones Dec 01 '15 at 16:53
  • 2
    Very useful, but it should be noted that this solution may not work in future versions of SQL Server. From the [CREATE TRIGGER documentation](https://msdn.microsoft.com/en-us/library/ms189799.aspx): *The ability to return results from triggers __will be removed in a future version of SQL Server__. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this.* – sstan Oct 26 '16 at 18:15
  • Why `select AddressId from [dbo].Address where @@ROWCOUNT > 0 and AddressId = scope_identity();` and not simply `SELECT scope_identity() WHERE @@ROWCOUNT > 0` ? (I haven't tried the solution, i'm just wondering why it's effectively `SELECT id FROM table WHERE id =1` rather than `select 1` – Caius Jard May 17 '18 at 12:26
15

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.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Can you elaborate on "but it will not help if the original query is executed as well." – Aducci Apr 09 '14 at 20:48
  • adding information to this answer, before insert does not exist in sql server, after insert is way slower than instead of insert. – Cassio Lemos Apr 29 '20 at 09:25
2

You also need to return any properties marked as Computed

select [Id], [YourComputedColumn]
from [dbo].[TableXXX]
where @@ROWCOUNT > 0 and [Id] = scope_identity()
Aducci
  • 26,101
  • 8
  • 63
  • 67
0

I also found that you need to have the StoreGeneratedPattern set to Identity to get it to work on a nvarchar column that I was using as a primary key but which was not generating an identity value. This was for situations of an after insert trigger which computed a unique value to store in the key column. In other situations (add and update), it may require being set to Computed.

user4815766
  • 1