CODEWITHSUNDEEP
c#sql-serverentity-frameworkazure-sql-database
Shawn Dillon
Shawn Dillon

Reputation: 181

Entity Framework 6 / SQL Server triggers - getting error

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

Upvotes: 17

Views: 25636

Answers (6)

Laila Abdulrahman
Laila Abdulrahman

Reputation: 21

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"));

Upvotes: 0

Bez70
Bez70

Reputation: 121

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

Upvotes: 5

Duck Ling
Duck Ling

Reputation: 2130

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

Upvotes: 9

BDarley
BDarley

Reputation: 1265

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)

Upvotes: 31

Wellspring
Wellspring

Reputation: 1340

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.

Upvotes: 2

Shawn Dillon
Shawn Dillon

Reputation: 181

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!

Upvotes: 1

Related Questions