29

I'm using Entity Framework 6 Code First, and would like to create a Trigger.

How do I do this?

The reason I need the trigger is because a user may either edit the database directly or through a program I'm writing, and I need to make sure 2 columns in a table are not both null, and are not both not null.

I've been looking and can't find a way.

Is there any way to specify a trigger using code first?

Charles W
  • 2,123
  • 3
  • 22
  • 37
  • Could be useful to add to a lib like [EntityFrameworkExtras](https://github.com/zzzprojects/EntityFrameworkExtras). – JoeBrockhaus May 08 '20 at 06:45

4 Answers4

28

Entity Framework has no support for triggers, although you can certainly manually execute a statement that would create a trigger, but you would need to do this after the table was created (if using migrations).

You can use the technique specified by Ladislav in EF 4.1 code-first adding a trigger to a table

Take note of his warning, however, EF will not be aware of any changes made in the trigger. If your intent is merely to ensure that 2 columns in a table are not null, you'd be better served with a constraint (constraints are also not supported by EF, but you can add them manually).

Amirhossein Mehrvarzi
  • 15,028
  • 7
  • 42
  • 69
Erik Funkenbusch
  • 91,709
  • 28
  • 188
  • 284
  • Thanks for the answer! Normally I would use a constraint, but, (I didn't mention) I'm planning to use MySQL as well, and MySQL has no support for constraints to my knowledge. – Charles W Feb 21 '14 at 20:03
  • Does the `[Required]` attribute satisfy the not null constraint? – Gusdor Jun 29 '16 at 10:56
  • 1
    @Gusdor - the problem is that he needs to ensure both columns are not null at the same time, while simultaneously allowing either to be null. The [Required] attribute would not do that. – Erik Funkenbusch Jun 29 '16 at 13:31
  • @ErikFunkenbusch Understood. – Gusdor Jun 29 '16 at 17:53
14

Check out my library EntityFramework.Triggers. It works at the Entity Framework layer, so the trigger events won't fire if someone modifies the database directly. The NuGet link is https://www.nuget.org/packages/EntityFramework.Triggers/

Nick Strupat
  • 4,737
  • 4
  • 41
  • 56
  • 10
    If you'd read the question you would have seen that he wanted a trigger on the database as the database can be modified directly. Your `triggers` aren't really triggers at all, just events on entities currently tracked by a DbContext. Considering the life of a DbContext is supposed to be quite short I don't really see the point. – kjbartel Mar 20 '15 at 07:01
  • A good library, but not quite what I'm after. If I open two contexts between two different applications, and trigger events they aren't shared. – wonea Feb 09 '17 at 13:22
  • @wonea that functionality is next on the list. I am thinking of adding support for message queues to pass trigger events across instances, applications, even machines. – Nick Strupat Jun 05 '17 at 15:01
6

After you add a migration, open the migration file and create your trigger as shown below

Note: you need to run update-database to see the changes in your database.

enter image description here

user2662006
  • 2,186
  • 20
  • 16
0

Maybe my library will be useful for someone, though it works only with the latest version of EfCore. It extends ModelBuilder syntax, translates it into SQL, and applies received code via migrationBuilder.Sql().

modelBuilder.Entity<Transaction>()
    .AfterUpdate(trigger => trigger
        .Action(action => action
            .Condition((transactionBeforeUpdate, transactionAfterUpdate) => transactionBeforeUpdate.IsVeryfied && transactionAfterUpdate.IsVeryfied) // Executes only if condition met 
            .Update<UserBalance>(
                (transactionBeforeUpdate, transactionAfterUpdate, userBalances) => userBalances.UserId == oldTransaction.UserId, // Will be updated entities with matched condition
                (oldTransaction, updatedTransaction, oldBalance) => new UserBalance { Balance = oldBalance.Balance + updatedTransaction.Value - oldTransaction.Value }))); // New values for matched entities.
Belyanskiy Ilya
  • 131
  • 2
  • 2