INSTEAD OF INSERT trigger causes error in SaveChanges of Entity Framework

Question

This is my first post, but I come here a lot for finding the right solutions for my problems.

I have a database (SQL Server 2008 R2) with a table that has an INSTEAD OF INSERT trigger for checking the inserted data.

If it's a duplicated row, update that row; otherwise, insert that row.

Trigger:

ALTER TRIGGER [dbo].[CheckDataTrigger]
    ON [dbo].[MonitorSummary]
    INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT OFF

    IF exists( select * from inserted i, MonitorSummary ms
            where ms.ServiceUrl = i.ServiceUrl
                AND ms.AppName = i.AppName
                AND ms.BuildVersion = i.BuildVersion
                AND ms.FunctionName = i.FunctionName
                AND ms.Company = i.Company
                AND ms.UserName = i.UserName
                AND ms.ServerName = i.ServerName)
    BEGIN
       UPDATE MonitorSummary  
       SET MonitorSummary.Function_Count = inserted.Function_Count,
           MonitorSummary.Execution_Time_Sum = inserted.Execution_Time_Sum,
           MonitorSummary.LogLevel = inserted.LogLevel
       FROM inserted
       WHERE MonitorSummary.ServiceUrl = inserted.ServiceUrl
       AND MonitorSummary.AppName = inserted.AppName
       AND MonitorSummary.BuildVersion = inserted.BuildVersion
       AND MonitorSummary.FunctionName = inserted.FunctionName
       AND MonitorSummary.Company = inserted.Company
       AND MonitorSummary.UserName = inserted.UserName
       AND MonitorSummary.ServerName = inserted.ServerName
    END
    ELSE
    BEGIN
      INSERT INTO MonitorSummary
         SELECT 
             i.ServiceUrl, i.EmailAdress, i.EmailSent, i.AppName, i.BuildVersion, 
             i.FunctionName, i.Company, i.UserName, i.ServerName, i.Function_Count, 
             i.Execution_Time_Sum, i.LogLevel
         FROM inserted i
    END

    SELECT Monitor_Id 
    FROM MonitorSummary 
    WHERE @@ROWCOUNT > 0 AND Monitor_Id = SCOPE_IDENTITY()
END

As you can see, SET NOCOUNT OFF and I do the select query at the end of the trigger. Extra information about the table:

CREATE TABLE [dbo].[MonitorSummary](
    [Monitor_Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceUrl] [nvarchar](100) NULL,
    [EmailAdress] [nvarchar](100) NULL,
    [EmailSent] [bit] NOT NULL default 0,
    [AppName] [nvarchar](100) NULL,
    [BuildVersion] [nvarchar](100) NULL,
    [FunctionName] [nvarchar](100) NULL,
    [Company] [nvarchar](100) NULL,
    [UserName] [nvarchar](100) NULL,
    [ServerName] [nvarchar](100) NULL,
    [Function_Count] [int] NOT NULL default 0,
    [Execution_Time_Sum] [numeric](18, 8) NOT NULL default 0,
    [LogLevel] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [Monitor_Id] ASC
)

I have tested the trigger before using it. it works great!

Test code:

INSERT INTO MonitorSummary VALUES
    ('ServiceUrl2', 'EmailAdress2', 0, 'AppName2', 'BuildVersion2', 'FunctionName2',
        'Company2', 'UserName2', 'ServerName2', 1, 1, 'LogLevel2'),
    ('ServiceUrl2', 'EmailAdress2', 0, 'AppName2', 'BuildVersion2', 'FunctionName2',
        'Company3', 'UserName2', 'ServerName2', 1, 1, 'LogLevel2'),                 
    ('ServiceUrl3', 'EmailAdress3', 0, 'AppName2', 'BuildVersion2', 'FunctionName2',
        'Company2', 'UserName2', 'ServerName2', 1, 1, 'LogLevel2'),
    ('ServiceUrl3', 'EmailAdress3', 0, 'AppName2', 'BuildVersion2', 'FunctionName2',
        'Company2', 'UserName2', 'ServerName2', 2, 2, 'LogLevel2')

NOW, the problem is that the EF (Entity Framework) doesn't work.

I think it's irrelevant that this works in a web service (.asmx), because I tested EF with a test table (without the trigger) and everything just works fine.

In this code, I add some objects to EF and save the changes.

Already tried to save the changes in the loop, but this it has no effect...

private void StoreData(MonitorSummary[] data)
{
    foreach (MonitorSummary item in data)
    {
        MonitorEntity.MonitorSummary.AddObject(item);
        //MonitorEntity.SaveChanges();
    }
    MonitorEntity.SaveChanges();
}

I always end with this error:

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.

I have also tried to Attach the object and change the concurrency mode to fixed, all of this doesn't work..

I even tried an AFTER INSERT trigger, but that is not the solution I wanted and I have to code a lot more since it's already inserted in the table..

Does anyone now a solution to my problem?

Answer 1

I believe that you need to set @@identity in UPDATE part of the trigger. To do so, create a simple insert query and execute it. Assuming there is identity column id in MonitorSummary:

declare @strSql varchar(1000)
declare @id int

select @id = max (id) 
  from MonitorSummary
 where -- whole join condition to identify changed record(s)

set @strSql = 'SELECT Identity (Int, ' + Cast(@id As Varchar(10)) + ',1) AS id 
                     INTO #Tmp'
EXECUTE (@strSql)

If you use Sql Server 2005 or newer there is output clause in UPDATE you can use to retrieve list of changed records.

Btw, you might remove exists part and check if UPDATE set @@rowcount to zero; if it did, you need to insert rows.