Trigger and Stored Procedure Management with EF Core

Using Entity Framework Core, we will list triggers and stored procedures and delete them in bulk. We will also refresh our triggers using a sample sql file. For those who prefer Code First, this article will be perfect. In this way, you will minimize your need for database access and ensure that your new changes are automatically applied to the database.

---

We ran our tests using MySQL.

You can use the following command to list your current Triggers.

select * from information_schema.TRIGGERS

You can use the following command to list the available Stored Procedures.

select * from information_schema.ROUTINES

Here are your active scripts in your database. Some are built by default and are used for your system needs. You can expand the following queries to filter only scripts you have created.

select * from information_schema.TRIGGERS where TRIGGER_SCHEMA = 'your_schema'
select * from information_schema.ROUTINES where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'your_schema'

You've seen how we filter our data. If you want old scripts to be renewed when you make changes to these parts in the future, I have developed a trick for you. You can put this process in a method and then trigger it in public void Configure(IApplicationBuilder app) method in Startup.cs. Thus, every time the project is up, it will replace the old scripts with new ones. Evaluate this triggering need carefully according to the risk situation of the project!

            var listTriggers = new List<string>();
            var listStoredProcedures = new List<string>();
            using (var command = _context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = "select group_concat(TRIGGER_NAME) from information_schema.TRIGGERS where TRIGGER_SCHEMA = 'your_schema'";
                command.CommandType = CommandType.Text;
                _context.Database.OpenConnection();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var value = reader[0].ToString();
                        if (!string.IsNullOrEmpty(value))
                            listTriggers.AddRange(value.Split(","));
                    }
                }

                command.CommandText = "select group_concat(ROUTINE_NAME) from information_schema.ROUTINES where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'your_schema'";
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var value = reader[0].ToString();
                        if (!string.IsNullOrEmpty(value))
                            listStoredProcedures.AddRange(value.Split(","));
                    }
                }
            }

            foreach (var item in listTriggers)
                _context.Database.ExecuteSqlRaw($"drop trigger if exists {item}");

            foreach (var item in listStoredProcedures)
                _context.Database.ExecuteSqlRaw($"drop procedure if exists {item}");

            var sqlFiles = new List<string> {"fnc", "stp", "trg"};
            foreach (var sqlFile in sqlFiles)
            {
                var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts", $"{sqlFile}.sql");
                var sql = File.ReadAllText(path);
                if (!string.IsNullOrEmpty(sql))
                    _context.Database.ExecuteSqlRaw(sql);
            }

Finally, I added Functions (fnc.sql)/Stored Procedure (stp.sql)/Trigger (trg.sql) under the Scripts folder by grouping them in separate folders. When the above code block runs, all stored procedures and triggers that match the filters will be deleted and the queries under the Scripts folder will be run and loaded again.

For example, the contents of the trg.sql file

create trigger trg_blogpost_view_count_after_update_user
    after update
    on blog_posts
    for each row
begin
    if (OLD.ViewCount + 1 = NEW.ViewCount) then
        update users set BlogPostViewCount = BlogPostViewCount + 1 where Guid = OLD.CreatedBy;
    end if;
end;