Add support for managing Triggers with EF migration

[Angelinsky7]

I've found this : https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations and i was thinking : Great i could maybe use this to handle the creation of my table triggers...
But sadly there is actually no way to tell the EntityTypeBuilder that i would like to have some triggers on my table... and i clearly think that a trigger should not only be in a single migration but also in current state of the entity (and that we could change/remove/update/etc the trigger and create a new migration for it)
So my question : Why ? Is there something difficult to manage with triggers ? Why could we add some custom operations on EntityTypeBuilder to add and manage those kind of things (and then handle those custom operation in the migration generator) ?

I was naively thinking that a trigger was :

• a name
• on a table in a schema
• with some "simple" properties
• and a custom code

I could understand that some database target of entityframework don't have triggers but we could easily send an exception saying : "Sorry this operation (....) is not supported by this database provider" and let the user choose to change the database provider or don't use the feature... no ?

Does not seem too difficult to handle, to create and to check changes, no ?
So, i clearly miss something, could someone tell why there's nothing to handle such kind of concept and help me understand the complexity...

Thanks for your amazing lib !!

[ajcvickers]

@Angelinsky7 This shouldn't be too difficult to do, it just doesn't have a high enough priority for us to have implemented it yet. It's also not 100% clear how much we work on this specific case as opposed to the extensibility that would allow these things to be done without EF Core having direct support--for example see #10258 Putting this on the backlog for now.

[Angelinsky7]

@ajcvickers thanks for your answer... if i find the time and you'ld accept pr, i'll check, the more we are the better, no ?

[ajcvickers]

@Angelinsky7 We always appreciate PRs. If you are going to submit a PR, please post here with the general approach you plan to take before doing too much work on it so that we can make sure it follows the direction we want to go in this area.

[MortenMeisler]

I would like this very much - I can't really see a way right now to handle things like LastModified on the database-level. There are many methods describing how to override SaveChanges and add custom triggers etc., but the logic for this should be put on the database, just like defaultvalues are.

So atm a workaround is simply to migrate custom sql in the Up() and Down() method:

public partial class triggers : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
            CREATE OR ALTER TRIGGER trg_Customers_UpdateTimeEntry
            ON dbo.Customers
            AFTER UPDATE
            AS
            UPDATE dbo.Customers
            SET LastUpdated = GETUTCDATE()
            WHERE Id IN(SELECT DISTINCT Id FROM Inserted)
                GO");
        }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
        DROP TRIGGER trg_Customers_UpdateTimeEntry");
    }
}

And this is ofc hardcoded, so would need to do some logic for constructing the string.

I can't see no other ways or what?

[roji]

@MortenMeisler as the trigger can contain totally arbitrary SQL code, what do you see as the value of having special EF Core support here? How is your current raw SQL approach any worse than whatever would be provided by this feature?

[MortenMeisler]

well to be honest I would just like a "computed on update" functionality, as the trigger can be anything yes and is sql specific. I thought that valuegeneratedonaddorupdate would do this, but no. Imo rather bloated framework with lots of features, I find myself doing this step here over and over

[roji]

If the motivation here is primarily to support timestamps being generated on update, it may be sufficient for the SqlServer provider to specifically set up the appropriate trigger for timestamps configured with on-update value generation. This would be much more usable/friendly than generic trigger management in migrations (users wouldn't have to actually know that triggers are involved etc.), and it seems like a common-enough scenario to add special support.

[mhosman]

This could be a great feature!

[AFDevMike]

We have a need to Audit some specific Entities that are identified with a Custom Attribute.
Some of the functionality of that trigger requires knowledge of Entity Names and Property Names as we create a facsimile of the row as JSON using SQL's FOR JSON.

Hooking into migrations would be the ideal place to keep in sync with model changes.

[MaxDZ8]

I tried using using https://github.com/win7user10/Laraue.EfCoreTriggers.

I don't mind EFCore itself not shipping this out of the box and I agree with the rationale of flowing annotations into migrations.

Yet I would like the options to be mentioned. Not necessarily raccomanded, let's say kinda like the symbiosis between .NET and Newtonsoft.JSON.

As it stands now the library has very little users but it has been updated 23 Oct. I think it demostrates value fairly well.

[roji]

@MaxDZ8 can you please submit a PR to this page? This is where we list all 3rd-party plugins and extensions.

[MaxDZ8]

Done, thank you.
PR 3556

[hintsofttech]

all triggers sql must be executed after all tables created because triggers are cross table operations
if executed before any table left will through exceptions...

[roji]

Since #27573 has morphed into doing the full infrastructure for triggers in metadata, we may as well also want to implement actual support for SQL Server/Sqlite. That would also make sure our infrastructure work is correct.

[AndriySvyryd]

Depends on #6546

[roji]

@AndriySvyryd maybe we're able to generate raw SQL?

[AndriySvyryd]

Yes, unless there are cases when they need to be created in the same statement as the table

[roji]

Makes sense. IIRC that isn't the case with the database I've seen - trigger creation is always a separate DDL.