Add triggers for generic types. about laraue.efcoretriggers

[sg-prasenjit]

I'm having same issue. did you get any alternative?

[LeonardoPiel]

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();

[sg-prasenjit]

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.