On Insert Trigger breaks EF Add

Question

I added a SQL Server table ON INSERT trigger and it broke my API's Entity Framework insert code. It throws an error:

InnerException = "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

I tried setting EntityState.Added; suggested by Ben here, but this didn't work.

I tried the suggestion from chamara here.

Added the following SQL Server trigger:

ALTER TRIGGER [dbo].[tr_TargetUserDel] 
ON [dbo].[TargetUser]
AFTER  INSERT
AS
BEGIN
    DECLARE @UserId AS INT
    DECLARE @TargetId AS INT
    DECLARE @TargetUserId AS INT

    SELECT @UserId = i.[UserId], @TargetId = i.[TargetId] 
    FROM inserted i

    INSERT INTO [dbo].[TargetUser] ([UserId], [TargetId])
    VALUES (@UserId, @TargetId)

    SELECT @TargetUserId = CAST(@@Identity AS INTEGER)

    DELETE [dbo].[TargetUser] 
    WHERE TargetUserId <> @TargetUserId 
      AND TargetId = @TargetId
END

This caused the following EF insert to fail:

targetuser.UserId = interfaceUser.UserId;
targetuser.TargetId = interfaceUserDTO.TargetId;

db.InterfaceTargetUsers.Add(targetuser);                    
db.SaveChanges();      // <<Error thrown

The model object targetuser:

[Table("TargetUser")]
public partial class InterfaceTargetUser
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TargetUserId { get; set; }
    public int UserId { get; set; }
    public int TargetId { get; set; }
}

EF is having trouble with the insert, but if I run the insert directly in SQL Server, it runs fine:

INSERT INTO targetuser (TargetId, UserId) 
VALUES (5276, 572)

Modifying the trigger as suggested by chamara here works, but am looking for a EF solution.

chamara's workaround is to add this to the end of the trigger:

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

This works but it has no up votes. Why does this work? Seems like there should be an EF solution?

`select @UserId = i.[UserId], @TargetId=i.[TargetId] from inserted i` That's wrong, what if `INSERTED` pseudo has more than 1 row? — Ilyes, May 07 '19 at 21:55
According to https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/modification-sql-generation EF will run a select statement with condision @@ROWCOUNT > 0 after insert, so if @@ROWCOUNT is wrong it will not work. — Piotr Palka, May 07 '19 at 22:00

Ilyes · Accepted Answer · 2019-05-08T02:41:50.227

You have many problems there:

select  @UserId = i.[UserId], @TargetId=i.[TargetId] from inserted i

Will works only when the pseudo INSERTED hase only 1 rows.

Also for the line

SELECT @TargetUserId =CAST(@@Identity AS INTEGER)

Will only return the last generated identity value, not all of them.

Alter your trigger to be like

ALTER TRIGGER [dbo].[tr_TargetUserDel] ON [dbo].[TargetUser]
AFTER  INSERT
AS
BEGIN
  SET NOCOUNT ON;       
  INSERT INTO [dbo].[TargetUser]([UserId], [TargetId])
  SELECT UserId,
         TargetId
  FROM INSERTED;

  DELETE T
  FROM [dbo].[TargetUser] T INNER JOIN INSERTED TT
  ON T.TargetId = TT.TargetId 
     AND
     T.TargetUserId <> TT.TargetUserId;
  SET NOCOUNT OFF; 
END

Hi @Sami - Written this way, will it solve the problem noted by **@Piotr** above? — bruce neiman, May 07 '19 at 22:41

On Insert Trigger breaks EF Add

1 Answers1