16

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()
{
}
Bastien Vandamme
  • 17,659
  • 30
  • 118
  • 200
  • 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 Mar 25 '19 at 11:31
  • Ah ok. Trigger is not standard? – Bastien Vandamme Mar 25 '19 at 13:41
  • 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 Mar 25 '19 at 16:40
  • No fluent API so far, so option (2) - `migrationBuilder.Sql("CREATE TRIGGER …")` etc. – Ivan Stoev Mar 25 '19 at 17:42
  • 1
    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 Mar 26 '19 at 01:00

2 Answers2

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

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"drop trigger <triggerName>");
    }
Tigran
  • 367
  • 6
  • 8
  • 1
    this fails when generating SQL because it tries to create a trigger from within a strored procedure – Chazt3n Dec 17 '20 at 19:21
  • @Chazt3n, can you provide the example you have tried? – Tigran Jan 07 '21 at 08:47
  • 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 – Chazt3n Jan 12 '21 at 00:49
10

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()
Belyansky Ilya
  • 331
  • 5
  • 4
  • thanks for this library, unfortunately when I try to write ```modelBuilder.Entity().AfterInsert(trigger => trigger .Action(ta => ta.Update( (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` – Dragouf Dec 31 '20 at 15:22
  • Have you used extension .UseTriggers() while configuring your DbContext? – Belyansky Ilya Jan 01 '21 at 12:32
  • 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 – Dragouf Jan 01 '21 at 12:54
  • Could you provide source code repository which reproduce the problem? – Belyansky Ilya Jan 02 '21 at 20:49