MERGE into a view with INSTEAD OF triggers

Question

I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view:

create table tbl (id uniqueidentifier not null primary key, data nvarchar(max) null)
go
create view vwTbl as select * from tbl
go
create trigger vwTblInsert on vwTbl instead of insert as
    insert into tbl (id, data)
    select id, data
    from inserted
go

Here's the approximate SQL that EF generates:

declare @inserted1 table ([id] uniqueidentifier);
merge vwTbl using (
    values (newid(), 'xxx') 
) as i (id, data) on 1=0
when not matched then
    insert (id, data)
    values (i.id, i.data)
    output inserted.Id
    into @inserted1;
select * from @inserted1

Here's the error I'm getting:

The column reference "inserted.id" is not allowed because it refers to a base table that is not being modified in this statement.

The help page for the merge statement says:

If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action done by a MERGE statement, it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

I believe I fulfil this condition and I can't see any other relevant limitations. Why doesn't it work?

It's EF who generates the query, not me. I have no control over it. I just want to understand why it doesn't work and if I can fix my trigger somehow to make it work.

Answer 1

Option 1

It is possible that the inserted reference is not allowed here because SQL Server does not have access to the value actually inserted by the instead of trigger on the view.

SQL Server instead-of triggers are implemented by writing information about the changes that would occur as a result of the triggering statement to worktables (one for inserts, one for deletes).

Instead-of triggers do not use row versioning, like after triggers do. These worktables are fully populated by the triggering statement before the trigger fires.

The OUTPUT clause is part of the triggering statement. The query plan for that statement only has access to values that exist at that time. The instead-of trigger fires afterward. Logically, the output clause cannot return values that have not been computed yet. You can see all this in the execution plans for your example:

In your implementation, the instead-of trigger essentially does nothing other than a regular insert would do, but that isn't guaranteed to be the case. An instead-of trigger can do anything it likes instead of the triggering action, including doing nothing.

You can get the statement to 'work' by referencing the source column value, but again this will only represent the actually inserted value if the instead-of trigger happens to do the same thing:

declare @inserted1 table ([id] uniqueidentifier);
merge vwTbl using (
    values (newid(), 'xxx') 
) as i (id, data) on 1=0
when not matched then
    insert (id, data)
    values (i.id, i.data)
    output i.id -- CHANGED
    into @inserted1;
select * from @inserted1

db<>fiddle demo

There is nothing you can do to change your view's instead of trigger so this pattern will work right now.

Option 2

On the other hand, it is also completely conceivable this is unintended behaviour. There have been many such cases with MERGE.

The equivalent statement written as an INSERT does work:

declare @inserted1 table ([id] uniqueidentifier);
INSERT dbo.vwTbl (id, data)
OUTPUT Inserted.id
INTO @inserted1 (id)
VALUES (NEWID(), 'xxx');

This follows the documented OUTPUT semantic for the inserted pseudo-table in this case (emphasis added):

INSERTED
Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

If MERGE worked the same way as INSERT here, your statement would work as expected and the OUTPUT clause would return the pre-trigger value. It is possible this is a deliberate design change. There was more than one of those made when MERGE was introduced.

On the other hand, the MERGE statement works as desired if the instead-of trigger is on the base table rather than a view (demo), so the current behaviour is inconsistent if nothing else.

The chance that this is a bug is increased by noting that it is possible to reference the deleted pseudo-table in your example, though it returns a null result, naturally. See this demo. The inference here is that the parser is getting confused about the resulting action (an insert, not a delete).

If you require a definitive answer, your best bet might be to open a support case with Microsoft.

Answer 2

This isn't an answer to the question as asked, but it might help you work around the problem if you can't overcome the issue with the trigger.

As you mentioned, EF Core does this MERGE technique to batch changes and avoid multiple round trips to the database. You can avoid that behavior, if you're willing to accept the overhead of multiple round trips, by calling SaveChanges() / SaveChangesAsync() after each new "row" is added to the DbContext. Doing this will cause EF to flush that single row to the database as an INSERT statement.

As an example, this is the kind of thing that results in a MERGE (add a bunch of things to the DbContext, than call SaveChanges):

using (var db = new BloggingContext())
{
    foreach (var blogUrl in newBlogUrls)
    {
        db.Add(new Blog { Url = blogUrl });
    }
    db.SaveChanges();
}

This is how you would "force" it to be INSERT statements instead (SaveChanges after each add):

using (var db = new BloggingContext())
{
    foreach (var blogUrl in newBlogUrls)
    {
        db.Add(new Blog { Url = blogUrl });
        db.SaveChanges();
    }
}

Answer 3

I know I'm late to the party but Josh Darnell actually sparked my memory with his good idea, as I encountered the same exact issue you're experiencing, but couldn't remember how I solved it until now.

Perhaps an even simpler way to disable batching in EF Core is by setting the MaxBatchSize option to 1 when configuring your database connection, as discussed here. Reference code:

protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
{
    string sConnString = @"Server=localhost;Database=EFSampleDB;Trusted_Connection=true;";
    optionbuilder.UseSqlServer(sConnString , b => b.MaxBatchSize(1));
}

By setting the MaxBatchSize to 1, you won't have to call db.SaveChanges() after every change in your application, rather EF Core will generate the appropriate SQL to do an individual INSERT for every change that needs to be committed, even when you have multiple uncommitted changes. Also documented here.