5

I can't insert an entity into the mapped view. The error I get is:

Store, insert or delete statement affected an unexpected number of rows (0)

I know how to use stored procedures, but it is more interesting for me to try a solution with an instead of trigger on insert row view event. I don't get any error when I delete, update or insert in t-sql code, but I can't INSERT a row using EF. Update and delete work in EF, but INSERT doesn't.

Code:

create view TestInsert
as
    select a.table_id, a.name
    from TableA as a

create trigger tr_works_via_tsql_but_not_ef_for_some_reason
on TestInsert
instead of isert
begin
   insert into TableA (table_id, name)
   select table_id, name from inserted;
end

[Table(TestInsert)]
public class TestInsert
{
   [Key]
   public int table_id { get; set; }
   public string name { get; set; }
}

Can anyone help me?

H. Pauwelyn
  • 13,575
  • 26
  • 81
  • 144
user1572418
  • 253
  • 2
  • 9
  • This is a similar question (but I don't have edmx file): http://stackoverflow.com/questions/12217169/entity-framework-with-instead-of-triggers – user1572418 Nov 12 '14 at 22:23

1 Answers1

18

Hooray, I have found the solution! The body of instead of trigger must return an id for the table.

create trigger tr_works_via_tsql_but_not_ef_for_some_reason
on TestInsert
instead of isert
begin
   insert into TableA (table_id, name)
   select table_id, name from inserted;

   **select id from TableA where @@ROWCOUNT > 0 and id = scope_identity()**
end

I use direct mapping and there is no edmx file in my project. This is the source of the answer: Entity Framework with Instead Of triggers

Community
  • 1
  • 1
user1572418
  • 253
  • 2
  • 9
  • Thank you for this, I've spent several hours trying to fix it. I couldn't use cascade delete because of the possible cycles, because of that I used instead of delete trigger. But also I had to use merge statement, and it required to add instead of insert and update triggers as well, after what EF create failed. – Dmitry Sikorsky Jul 07 '23 at 09:36