Is it possible and how create a trigger with Entity Framework Core

Question

Is it possible to create a SQL trigger with Entity Framework Core.

Maybe by using instruction in

protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
{
}

Or simply by executing SQL statements in Migration scripts

public override void Up()
{
}

Victual

Triggers are highly vendor-specific - so please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. – Vollmer

Ah ok. Trigger is not standard? – Victual

In theory: yes - pretty much every serious RDBMS has triggers. But the exact syntax and their capabilities vary quite a bit from product to product .... – Vollmer

No fluent API so far, so option (2) - migrationBuilder.Sql("CREATE TRIGGER …") etc. – Jenisejenkel

And it is not possible to extend the fluent API to create an action that will fill the Up() method? I guess no because I don't know then how fluent API can compare with snapshot. I'm very disappointed by this Core version of EF because I was really expecting a improvement of how defining his model and have more control on DB. – Victual

Answer 1

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"create trigger .....");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"drop trigger <triggerName>");
    }

Singlebreasted

this fails when generating SQL because it tries to create a trigger from within a strored procedure – Veronaveronese

@Chazt3n, can you provide the example you have tried? – Singlebreasted

thank you for reminding me about this. This WON'T fail unless you specify --idempotent when generating SQL. I did not know this had been changed in our CI Pipeline – Veronaveronese

Answer 2

Laraue.EfCoreTriggers package for creating SQL triggers through fluent syntax which allows to execute insert, update, upsert, delete, insert if not exists statements after trigger has worked like this

modelBuilder.Entity<Transaction>()
    .AfterInsert(trigger => trigger
        .Action(triggerAction => triggerAction
            .Upsert(transaction => new { transaction.UserId },
                insertedTransaction => new UserBalance { UserId = transaction.UserId, Balance = insertedTransaction.Sum },
                (insertedTransaction, oldBalance) => new UserBalance { Balance = oldBalance.Balance + insertedTransaction.Sum })));
            

This code will be translated into sql and applied to migrations using

migrationBuilder.Sql()

Hogfish

thanks for this library, unfortunately when I try to write modelBuilder.Entity<AvailabilitySettings>().AfterInsert(trigger => trigger .Action(ta => ta.Update<AvailabilitySettings>( (insertedEntity, searchedEntity) => insertedEntity.Id == searchedEntity.Id, (insertedEntity, foundEntity) => (new AvailabilitySettings(foundEntity) { Revision = foundEntity.Id })) )); nothing is generated in Up method when executing dotnet ef migrations add AMigration – Tobar

Have you used extension .UseTriggers() while configuring your DbContext? – Hogfish

Yes I did. Maybe just a thing to tell. My dbcontext is in a separate library. And I use a connection string from inside the class hard coded for migrations commands – Tobar

Could you provide source code repository which reproduce the problem? – Hogfish