Entity Framework 6 / SQL Server triggers - getting error

Question

I have an EF6 solution that I would like to add a trigger on a table to log changes to a new table. This is due to an integration we are doing to an external database. Basically, they want a log of changes that are made to a table for sync purposes.

The triggers work perfectly when I execute through SSMS (Azure SQL DB), but when I test via our web app I get the following error:

(0x80131904): The target table 'DestinationTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Basically, it looks like I can't assign a trigger to a table that uses Entity Framework.

Anyone have ideas for how to make this work?

Thanks in advance

Groats

The error is actually telling you that you can't use an OUTPUT without an INTO clause, not "You cannot use triggers in Entity Framework". The error is telling you the problem here; if you have an OUTPUT it need to have an INTO as well. – Subsequence

The error is only being triggered when an operation is executed via EF. So, I guess that the underlying SQL being generated by EF uses OUTPUT without an INTO. Is there a way to modify the EF process to comply with the requirements that you know of? – Groats

Are you sure this is EF6 and not EF Core with batching enabled. AFAIK EF6 doesn't use OUTPUT. – Pellitory

EF6 vs EF Core version 6? hhhmm This is very confusing. I think I'm running into this now actually. I have something called ef core and 6.0.21....is that ef6 or something else? Either way, the trigger stuff is still broken AND with 6.0.21 the .Conventions doesn't compile. – Battlement

Answer 1

Encountered the same error using EF Core 7.X

I had a trigger on a table and upon insert an error was returned.

The target table 'MyTable' of the DML statement cannot have any enabled 
triggers if the statement contains an OUTPUT clause without INTO clause

If I gutted the trigger with no logic I still received the error. Disabling the trigger resolved the error. But, if I performed a manual insert, no error was returned from SSMS.

Solution for me, (Let the DBContext entity object know that a trigger is on the table).

Update Fluent API to ...

builder.Entity<MyTable>(entry =>
    {
       entry.ToTable("MyTable", tb => tb.HasTrigger("MyTable_Insert"));
    });

More information can be found at the Microsoft site pertaining to this topic Breaking changes in EF Core 7.0 (EF7)

Broz

What if the table has more than 1 trigger? – Arnoldarnoldo

builder.Entity<MyTable>(entry => { entry.ToTable("MyTable", tb => { tb.HasTrigger("MyTable_Insert"); tb.HasTrigger("MyTable_Update"); }); }); – Broz

One HasTrigger statement is enough. It only informs EF that the table has triggers. Even the trigger name doesn't matter. – Sternutation

do you know how to add this, with a database first approach? Meaning, there are no migrations to add this code to.... – Imamate

Answer 2

My issue was similar - also regarding a breaking change in EF Core 7.X but I followed an alternative route, also present in the docs, since all of my tables have triggers...

As stated in Microsoft docs, in the Mitigation section, the below should help:

If most or all of your tables have triggers, you can opt out of using the newer, efficient technique for all your model's tables by using the following model building convention:

public class BlankTriggerAddingConvention : IModelFinalizingConvention
{
    public virtual void ProcessModelFinalizing(
        IConventionModelBuilder modelBuilder,
        IConventionContext<IConventionModelBuilder> context)
    {
        foreach (var entityType in modelBuilder.Metadata.GetEntityTypes())
        {
            var table = StoreObjectIdentifier.Create(entityType, StoreObjectType.Table);
            if (table != null
                && entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(table.Value) == null))
            {
                entityType.Builder.HasTrigger(table.Value.Name + "_Trigger");
            }

            foreach (var fragment in entityType.GetMappingFragments(StoreObjectType.Table))
            {
                if (entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(fragment.StoreObject) == null))
                {
                    entityType.Builder.HasTrigger(fragment.StoreObject.Name + "_Trigger");
                }
            }
        }
    }
}

Use the convention on your DbContext by overriding ConfigureConventions:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Conventions.Add(_ => new BlankTriggerAddingConvention());
}

Velites

Please see this meta post. I think it's better to just mention this in a comment. – Sternutation

Hey Gert, thanks. I've updated the answer based on your links answers. I think it's a bit wordy for a comment, especially with code formatting. Most importantly it's a boilerplate answer to the problem at hand, which I've not managed to find directly on SO! – Velites

Answer 3

For those now targeting Entity Framework Core 8 they have now implemented the ability to use or not use the "OUTPUT" clause on a table by table basis.

From the Microsoft Docs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable(tb => tb.UseSqlOutputClause(false));
}

Croner

Answer 4

It's an obscure edge case, but I found myself here because I was seeing this error using Microsoft.EntityFrameworkCore.SqlServer 7.0.0-preview.

When I reverted to 6.0.8, the bug went away.

Serves me right for developing against a preview edition.

Scaffolding

This is still true with latest stable 7.0.0 - I downgraded to 6.0.11 and the error went away. Our triggers didn't have an OUTPUT clause at all. – Aeriell

Wow, @Aeriell thanks for the heads up. I'll sit tight a bit longer, I guess. – Scaffolding

Ah snap. I downgraded to 6.0.21, to try to get away from this error, but it's still impacting me. :< Frustrating. – Battlement

Answer 5

I have found the issue, and it is related to an extension package that we are using called ZEntity.BulkOperations.Extension. David Browne's comments made me log the error and an inner exception pointed to the ZEntity BulkInsert as the error. I then discovered this link https://github.com/zzzprojects/EntityFramework-Extensions/issues/334 , which pointed out that this package DID handle triggers. All I had to do was to stop and restart the services using the db and the package, so that the newly added trigger would be discovered by ZEntity.

TLDR; I just had to stop and start the services to make the ZEntity package aware of the added trigger.

Thank you to all who answered!

Groats

Answer 6

i find the same error so i solve it by folow these steps 1-Scaffold-DbContext "Server=ServerName;Database=databseName;USER Id=userName;Password=databasepasword;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -table tableThatHasTraiger -outputDir fileToMakeModel i notice that HasTrigger("theTriggerNameOfTable")) added entity.ToTable("Table", "dblpg", tb => tb.HasTrigger("theTriggerNameOfTable"));

Seldon