Add database trigger with Entity Framework Code First Migrations

Question

I use Entity Framework Migrations for code first to control my database model. It works charming and I can handle until now everything. But now I need to add one database trigger and I would like to do this with EF Migrations and not use a separate sql script for only this case (This would be confusing for clients, esp. after we convinced them that we can handle everything with EF Migrations). My trigger is straight forward and looks like tis:

CREATE OR REPLACE TRIGGER [name] BEFORE UPDATE ON myTable ...

Is there a command to add a trigger to EF Migrations?

Check out the solution I came up with: EntityFramework.Triggers. It's on NuGet as well. — Nick Strupat
– Nick Strupat, Commented Oct 4, 2014 at 18:20
Looks promising. Do you know, if it supports several database systems (oracle, sql server etc) — StefanG
– StefanG, Commented Oct 4, 2014 at 18:29
I've only tested it with SQL Server, but it's all built on top of Entity Framework in a provider-agnostic way. It depends on DbContext. — Nick Strupat
– Nick Strupat, Commented Oct 4, 2014 at 20:33
@NickStrupat To clarify, this doesn't actually create database triggers, but emulates something close to them in EF using events, correct? — Trevortni
– Trevortni, Commented Feb 28, 2023 at 19:49

Andy Brown · Accepted Answer · 2014-02-12 15:57:29Z

48

You can just add a Sql("SQL COMMAND HERE") method call to your migration's Up method. Don't forget to also add the drop statement to the Down method. You can create an empty migration if you need, just by running Add-Migration without any changes to the model.

public partial class Example : DbMigration
{
    public override void Up()
    {
        Sql("CREATE OR REPLACE TRIGGER [name] BEFORE UPDATE ON myTable ...");
    }

    public override void Down()
    {
        Sql("DROP TRIGGER [name]");
    }
}

answered Feb 12, 2014 at 15:57

Andy Brown

19.2k3 gold badges55 silver badges62 bronze badges

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

6 Comments

eoghank Over a year ago

You can also add IF NOT EXISTS(select * from sys.triggers where name = 'name') to UP() to make it idempotent.

Andy Brown Over a year ago

@eoghank. I'm not sure I agree. The idea of migrations is that they should be repeatable in all directions, and that they therefore prevent that situation occurring. In theory, if we need existence checks, we're not using migrations correctly - as we don't do existence checking for tables, we shouldn't for triggers?

wye Over a year ago

For EF core, use migrationBuilder.Sql("blah blah...");

korulis Over a year ago

If write such migrations (either for ef, ef core or fluent migrator) I can not use the "Down" part. Example: 1st migration creates a trigger. 2nd migration updates the trigger. I migrate db. Its nice.. the second migration did not fail, because it replaced the trigger from the first migration, but now.. I want to downgrade one step. Ups! I am, technically at migration 1, but there is no trigger bcs it was deleted by the Down step of migration 2.

Aleksey Cherenkov Over a year ago

@korulis, 2nd migration is update, so "Down" for it should be update to the original state (not drop)

|

Belyansky Ilya · Accepted Answer · 2020-11-09 19:01:49Z

Recently I faced similar problem and didn't find a solution to not write sql manually. So I wrote a little package which allows to write migrations using Ef Core entity builder 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 will be looks like

public partial class AddTriggers : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE FUNCTION LC_TRIGGER_AFTER_INSERT_TRANSACTION() RETURNS trigger as $LC_TRIGGER_AFTER_INSERT_TRANSACTION$ BEGIN INSERT INTO user_balances (user_id, balance) VALUES (NEW.user_id, NEW.sum) ON CONFLICT (user_id) DO UPDATE SET balance = user_balances.balance + NEW.sum; RETURN NEW;END;$LC_TRIGGER_AFTER_INSERT_TRANSACTION$ LANGUAGE plpgsql;CREATE TRIGGER LC_TRIGGER_AFTER_INSERT_TRANSACTION AFTER INSERT ON transactions FOR EACH ROW EXECUTE PROCEDURE LC_TRIGGER_AFTER_INSERT_TRANSACTION();");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP TRIGGER LC_TRIGGER_AFTER_INSERT_TRANSACTION ON transactions;DROP FUNCTION LC_TRIGGER_AFTER_INSERT_TRANSACTION();");
    }
}

Maybe it will be helpful for someone.

Collectives™ on Stack Overflow

Add database trigger with Entity Framework Code First Migrations

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related