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

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. — marc_s
– marc_s, Commented Mar 25, 2019 at 11:31
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 .... — marc_s
– marc_s, Commented Mar 25, 2019 at 16:40
No fluent API so far, so option (2) - migrationBuilder.Sql("CREATE TRIGGER …") etc. — Ivan Stoev
– Ivan Stoev, Commented Mar 25, 2019 at 17:42
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. — Bastien Vandamme
– Bastien Vandamme, Commented Mar 26, 2019 at 1:00

Tigran · Accepted Answer · 2020-02-18 09:41:49Z

13

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

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

answered Feb 18, 2020 at 9:41

Tigran

4057 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chazt3n Over a year ago

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

Tigran Over a year ago

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

Chazt3n Over a year ago

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

Belyansky Ilya · Accepted Answer · 2020-11-14 14:55:52Z

12

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

answered Nov 14, 2020 at 14:55

Belyansky Ilya

4016 silver badges4 bronze badges

4 Comments

Dragouf Over a year ago

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

Belyansky Ilya Over a year ago

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

Dragouf Over a year ago

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

Belyansky Ilya Over a year ago

Could you provide source code repository which reproduce the problem?

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related