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

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)

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());
}