Open LeonardoPiel opened 7 months ago
I'm having same issue. did you get any alternative?
No. I needed to do it by another way. I created a static class that is executed when user executes database-update. My problem context is: I needed to create triggers whenever a table is created. My approach was: delete all triggers and recreate the triggers when user executes database-update.
public static class ExecuteTriggersSQL
{
public async static Task Execute()
{
var config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json")
.Build();
var builder = new DbContextOptionsBuilder<DefaultDbContext>()
.UseSqlServer(config.GetConnectionString("DefaultConnection"),
b => b.MigrationsAssembly("Persistence"));
builder.EnableSensitiveDataLogging();
using (var dbContext = new DefaultDbContext(builder.Options))
{
var executeOnUpdateDatabase = Environment.GetEnvironmentVariable("ExecuteOnUpdateDatabase");
if (!string.IsNullOrEmpty(executeOnUpdateDatabase) && executeOnUpdateDatabase.ToLower() == "true")
{
Console.WriteLine("Applying Migrations");
dbContext.Database.Migrate();
Console.WriteLine("Done.");
Console.WriteLine("Triggers initializing");
var sql = "DECLARE @TableName NVARCHAR(MAX);\r\nDECLARE table_cursor CURSOR FOR\r\nWITH OnDelete AS\r\n( SELECT f.parent_object_id,\r\n f.referenced_object_id,\r\n RecursionLevel = 1,\r\n ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(OBJECT_NAME(f.parent_object_id), ' --> ', OBJECT_NAME(f.referenced_object_id)))\r\n FROM sys.foreign_keys AS f\r\n WHERE f.delete_referential_action_desc = 'CASCADE'\r\n UNION ALL\r\n SELECT od.parent_object_id,\r\n f.referenced_object_id,\r\n od.RecursionLevel + 1,\r\n ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(od.ObjectTree, ' --> ', OBJECT_NAME(f.referenced_object_id)))\r\n FROM OnDelete AS od\r\n INNER JOIN sys.foreign_keys AS f\r\n ON f.parent_object_id = od.referenced_object_id\r\n AND f.delete_referential_action_desc = 'CASCADE'\r\n)\r\nSELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE '%AspNet%' AND table_name <> '__EFMigrationsHistory'\r\nEXCEPT\r\nSELECT DISTINCT BaseTable = OBJECT_NAME(od.parent_object_id)\r\nFROM OnDelete AS od\r\nWHERE NOT EXISTS\r\n ( SELECT 1\r\n FROM OnDelete AS ex\r\n WHERE ex.parent_object_id = od.parent_object_id\r\n AND ex.ObjectTree LIKE CONCAT(od.ObjectTree, '%')\r\n AND LEN(ex.ObjectTree) > LEN(od.ObjectTree)\r\n )\r\n AND OBJECT_NAME(od.parent_object_id) NOT LIKE '%AspNet%'\r\n\r\nOPEN table_cursor;\r\n\r\nFETCH NEXT FROM table_cursor INTO @TableName;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n DECLARE @TriggerName NVARCHAR(MAX) = 'tr_instead_of_update_delete_' + @TableName;\r\n\r\n DECLARE @SqlScript NVARCHAR(MAX) = '\r\n IF OBJECT_ID(''' + @TriggerName + ''', ''TR'') IS NOT NULL\r\n BEGIN\r\n DROP TRIGGER ' + @TriggerName + ';\r\n END;';\r\n EXEC sp_executesql @SqlScript;\r\n\r\n FETCH NEXT FROM table_cursor INTO @TableName;\r\nEND\r\n\r\nCLOSE table_cursor;\r\nDEALLOCATE table_cursor;";
await dbContext.Database.ExecuteSqlRawAsync(sql);
sql = "DECLARE @TableName NVARCHAR(MAX);\r\nDECLARE table_cursor CURSOR FOR\r\nWITH OnDelete AS\r\n( SELECT f.parent_object_id,\r\n f.referenced_object_id,\r\n RecursionLevel = 1,\r\n ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(OBJECT_NAME(f.parent_object_id), ' --> ', OBJECT_NAME(f.referenced_object_id)))\r\n FROM sys.foreign_keys AS f\r\n WHERE f.delete_referential_action_desc = 'CASCADE'\r\n UNION ALL\r\n SELECT od.parent_object_id,\r\n f.referenced_object_id,\r\n od.RecursionLevel + 1,\r\n ObjectTree = CONVERT(VARCHAR(MAX), CONCAT(od.ObjectTree, ' --> ', OBJECT_NAME(f.referenced_object_id)))\r\n FROM OnDelete AS od\r\n INNER JOIN sys.foreign_keys AS f\r\n ON f.parent_object_id = od.referenced_object_id\r\n AND f.delete_referential_action_desc = 'CASCADE'\r\n)\r\nSELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name NOT LIKE '%AspNet%' AND table_name <> '__EFMigrationsHistory'\r\nEXCEPT\r\nSELECT DISTINCT BaseTable = OBJECT_NAME(od.parent_object_id)\r\nFROM OnDelete AS od\r\nWHERE NOT EXISTS\r\n ( SELECT 1\r\n FROM OnDelete AS ex\r\n WHERE ex.parent_object_id = od.parent_object_id\r\n AND ex.ObjectTree LIKE CONCAT(od.ObjectTree, '%')\r\n AND LEN(ex.ObjectTree) > LEN(od.ObjectTree)\r\n )\r\n AND OBJECT_NAME(od.parent_object_id) NOT LIKE '%AspNet%'\r\n\r\nOPEN table_cursor;\r\n\r\nFETCH NEXT FROM table_cursor INTO @TableName;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n DECLARE @TriggerName NVARCHAR(MAX) = 'tr_instead_of_update_delete_' + @TableName;\r\n\r\n DECLARE @SqlScript NVARCHAR(MAX) = 'CREATE TRIGGER ' + @TriggerName + '\r\n ON dbo.' + @TableName + '\r\n INSTEAD OF UPDATE, DELETE\r\n AS\r\n BEGIN\r\n RAISERROR(''Updates and deletes are not allowed ON dbo.' + @TableName + ' table'', 16, 1);\r\n END;';\r\n EXEC sp_executesql @SqlScript;\r\n\r\n FETCH NEXT FROM table_cursor INTO @TableName;\r\nEND\r\n\r\nCLOSE table_cursor;\r\nDEALLOCATE table_cursor;";
await dbContext.Database.ExecuteSqlRawAsync(sql);
Console.WriteLine("Triggers ending");
}
}
}
}
And then on program.cs:
await ExecuteTriggersSQL.Execute();
ahh I was thinking about taking a different approach. where i work with MigrationsModelDiffer class to create a static implementation for each table. which should trigger like this library. but i got a verry little success with it. i need to investigate the library to make it work.
var entities = modelBuilder.Model.GetEntityTypes().ToList();
entities.ForEach(entity => { var entityType = entity.GetType(); var fks = entity.GetForeignKeys().ToList(); if(fks.Count == 0) { modelBuilder.Entity(entityType)/ EXTENSION METHODS HERE/ } });
Is there a way to create triggers by the sample code above? I actually know that using modelBuilder.Entity() is possible. But I would like to create a generic trigger generator.