Warn when the model declares a trigger that migrations do not create

[kirill-abblix]

What problem are you trying to solve?

ToTable(t => t.HasTrigger("...")) declares a trigger in the model. dotnet ef migrations add writes that declaration into the model snapshot, emits no DDL for it, and says nothing. A clean build plus a successful database update therefore produces a database where the trigger the model names does not exist, and no warning is issued anywhere along the way.

HasTrigger not creating the trigger is by design, and I am not asking for it to start. The gap is that EF records the declaration and never compares it against the schema its own migrations produce, even though both sides are available in the same method at scaffolding time.

A trigger is usually declared because it enforces something the application must not be able to bypass. In our case it blocked UPDATE and DELETE on an audit table. The model declared it, the migration applied without complaint, and on the resulting database both statements succeeded against a populated table.

EF has other declarations migrations do not create - ToView and HasDbFunction among them, and the managing migrations page names five such objects. Two things separate the trigger case. A missing view or function fails loudly on first use; a missing trigger fails silently, and only when the guarantee is needed. And EF already persists triggers into the snapshot, so the comparison is against data EF already holds.

How to see it

using Microsoft.EntityFrameworkCore;

public class AuditEntry
{
    public int Id { get; set; }
    public string EventType { get; set; } = null!;
}

public class AppContext(DbContextOptions<AppContext> options) : DbContext(options)
{
    public DbSet<AuditEntry> AuditEntries => Set<AuditEntry>();

    protected override void OnModelCreating(ModelBuilder b) =>
        b.Entity<AuditEntry>(e =>
        {
            e.ToTable("audit_entries", t => t.HasTrigger("audit_entries_append_only"));
            e.HasKey(x => x.Id);
        });
}

A design-time factory, since the context has no parameterless constructor:

using Microsoft.EntityFrameworkCore.Design;

public class AppContextFactory : IDesignTimeDbContextFactory<AppContext>
{
    public AppContext CreateDbContext(string[] args) =>
        new(new DbContextOptionsBuilder<AppContext>()
            .UseNpgsql("Host=localhost;Database=trigger_repro;Username=postgres;Password=postgres")
            .Options);
}

Against a local PostgreSQL (docker run -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:18):

• dotnet ef migrations add InitialSchema - the Up/Down body contains only CreateTable/DropTable for audit_entries, no trigger DDL. EF does record the annotation: grep -rni trigger Migrations/ finds t.HasTrigger("audit_entries_append_only") in both <timestamp>_InitialSchema.Designer.cs and AppContextModelSnapshot.cs. So the fact is in the snapshot and no operation is produced from it.
• dotnet ef database update - succeeds.
• select tgname from pg_trigger t join pg_class c on c.oid = t.tgrelid where c.relname = 'audit_entries' and not t.tgisinternal; - returns zero rows.
• insert into audit_entries ("EventType") values ('created'); then update audit_entries set "EventType" = 'x'; reports UPDATE 1, and delete from audit_entries; reports DELETE 1. Both succeed against a populated table, so the guarantee the model declared is not there. The insert matters: a row-level append-only trigger never fires on an empty table, where both statements would succeed either way.

migrations add and database update both complete normally with nothing said about the trigger. Adding LogTo(Console.WriteLine, LogLevel.Information) and forcing the model to build logs nothing about it either, so the silence is not a logging-level question.

Describe the solution you'd like

• A warning from migrations add. MigrationsScaffolder.ScaffoldMigration already holds every input the check needs in one method: the previous snapshot model, the target model, and the up/down operation lists from IMigrationsModelDiffer.GetDifferences. A few lines further down it already inspects that operation list for a different reason and reports through IOperationReporter (the IsDestructiveChange warning). A trigger declared on a table whose operations carry no matching DDL is the same kind of report.

  This cannot live in RelationalModelValidator, which runs at model finalization with no migrations assembly, no snapshot and no operation list. So it is not a model-validation rule. Anyone managing the trigger elsewhere can silence the warning through ConfigureWarnings.

• A sentence in the HasTrigger documentation. The XML summary is "Configures a database trigger on the table", which reads as creating one, and the aka.ms/efcore-docs-triggers link resolves to the SQL Server provider page, whose trigger section never mentions HasTrigger. Document database triggers model building APIs EntityFramework.Docs#4522 is open and labelled undocumented-feature.

  One sentence saying the annotation creates nothing, and that the trigger must be added through migrationBuilder.Sql, would have prevented our case outright. It is also the cheapest of the three to ship.

• Optionally, a way to attach the DDL to the declaration so the two cannot diverge. That is Enable compositional approach of custom migration functionality #17740 applied to triggers, so it belongs with that issue.

Versions

• EF Core 10.0.10
• Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3, PostgreSQL 18
• .NET 10

The missing DDL is not provider-specific: MigrationsModelDiffer has no trigger handling at all (no occurrence of "trigger" in MigrationsModelDiffer.cs), so no provider can be handed a trigger operation. I reproduced on Npgsql only.

On SQL Server the declaration additionally has a runtime effect I have not measured but which is visible in source: SqlServerOutputClauseConvention is the only implementor of ITriggerAddedConvention, and it calls UseSqlOutputClause(false). So there a HasTrigger with no trigger behind it suppresses the OUTPUT clause on a false premise, while on Npgsql nothing reads the metadata and the declaration is inert. Either way it is a claim about the database that EF stores and never verifies.

Related

• Add support for managing Triggers with EF migration #10770 - the umbrella request for EF to manage triggers through migrations, in Backlog since 2018. This is deliberately narrower and does not depend on it: a warning requires no trigger DDL and stays useful even if Add support for managing Triggers with EF migration #10770 is never implemented.
• Document database triggers model building APIs EntityFramework.Docs#4522 - already open and labelled undocumented-feature; the documentation item above belongs there.
• Scaffolding: not adding HasTrigger #30253 (closed as a duplicate of the shipped SQL Server: Scaffold HasTrigger if the table contains triggers #28185) - the reverse direction, scaffolding not emitting HasTrigger.
• Enable compositional approach of custom migration functionality #17740 - a compositional approach to custom migration functionality; the third item above is that issue applied to triggers.