Entity Framework Core Advanced

Triggers and EF Core

Tables with triggers need one line of EF Core configuration, or SaveChanges fails on SQL Server. This lesson explains why, shows HasTrigger, how to read values a trigger sets, how to create the trigger in a migration, and how bulk updates fire triggers.

SaveChanges on a table with a trigger
  1. StartYou call SaveChangesAsyncEF Core collects the inserts, updates and deletes it is tracking
  2. Step 2EF Core checks the modelBecause of HasTrigger, it plans OUTPUT INTO a temp table instead of plain OUTPUT
  3. Step 3SQL Server runs the statementRows are written inside a transaction
  4. Step 4The trigger firesIt sees inserted and deleted and writes audit rows in the same transaction
  5. Step 5EF Core reads back generated valuesKeys and ValueGeneratedOnAddOrUpdate columns are copied onto your entities
  6. EndThe transaction commitsIf the trigger fails, nothing is saved

The idea in plain words

Since EF Core 7, SaveChanges on SQL Server uses the OUTPUT clause to read back generated values such as identity keys in one fast round trip. SQL Server does not allow a plain OUTPUT clause on a table that has a trigger, so saving fails with: The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The fix is to tell EF Core which tables have triggers. It then uses slightly different SQL (OUTPUT INTO a temporary table) that works with triggers.

Tell EF Core about the trigger

The trigger name is only used to choose the right SQL; EF Core does not create or check the trigger. In EF Core 8 and later you can instead turn the OUTPUT clause off for the table.

C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .ToTable(tb => tb.HasTrigger("trg_Products_PriceAudit"));

    // EF Core 8+: alternative with the same effect
    // modelBuilder.Entity<Product>()
    //     .ToTable(tb => tb.UseSqlOutputClause(false));
}

Reading values a trigger sets

If a trigger fills a column, for example UpdatedAt, the entity in memory still holds the old value after SaveChanges. Mark the property as generated by the database and EF Core reads it back as part of the save, or reload the entity.

C#
modelBuilder.Entity<Product>()
    .Property(p => p.UpdatedAt)
    .ValueGeneratedOnAddOrUpdate();      // read back after every insert and update

// or, for a one-off
await db.Entry(product).ReloadAsync();

Create the trigger in a migration

Add an empty migration and put the trigger SQL in it. EF Core sends each migrationBuilder.Sql call as its own batch, which CREATE TRIGGER requires.

C#
public partial class AddPriceAuditTrigger : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("""
            CREATE OR ALTER TRIGGER dbo.trg_Products_PriceAudit
            ON dbo.Products AFTER UPDATE
            AS
            BEGIN
                SET NOCOUNT ON;
                INSERT INTO dbo.PriceAudit (ProductId, OldPrice, NewPrice)
                SELECT d.ProductId, d.Price, i.Price
                FROM inserted i JOIN deleted d ON d.ProductId = i.ProductId
                WHERE i.Price <> d.Price;
            END
            """);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
        => migrationBuilder.Sql("DROP TRIGGER IF EXISTS dbo.trg_Products_PriceAudit;");
}

Triggers with huge data

  • ExecuteUpdateAsync and ExecuteDeleteAsync send one UPDATE or DELETE statement, so the trigger fires once with every changed row in inserted and deleted. A set-based trigger handles 1 million rows; a row-by-row trigger will crawl.
  • SaveChanges batches many changes into a few statements, so each statement can also carry many rows into the trigger.
  • SqlBulkCopy skips triggers unless you pass SqlBulkCopyOptions.FireTriggers.
  • A slow trigger makes every SaveChanges on that table slow and holds locks longer, so keep trigger work small.

Common mistakes

  • Adding a trigger to a table in production and only then discovering that SaveChanges fails, because HasTrigger was missing.
  • Expecting the entity in memory to show a value the trigger changed without ValueGeneratedOnAddOrUpdate or a reload.
  • Writing the trigger for one row (SELECT @id = Id FROM inserted), which breaks when EF Core saves several rows in one statement.
  • Putting business rules in triggers that the C# code does not know about, so behaviour surprises the next developer.

Interview question

Why does SaveChanges fail on a table with a trigger in EF Core 7 and later, and how do you fix it?

EF Core 7+ uses a single statement with an OUTPUT clause to save rows and read back generated values on SQL Server. SQL Server rejects an OUTPUT clause without INTO on a table that has enabled triggers. Configuring the table with ToTable(tb => tb.HasTrigger("TriggerName")) makes EF Core use OUTPUT INTO a temporary table, which works with triggers. EF Core 8 also offers UseSqlOutputClause(false).

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.