SQL Server多外键指向同表时动态创建INSTEAD OF DELETE触发器

原脚本的问题 #

• 原脚本逐条处理LF_DB_Relations中的关系记录，每处理一条就为对应父表创建一个新的INSTEAD OF DELETE触发器。但SQL Server明确规定单个表只能存在一个同类型的INSTEAD OF触发器，因此当父表关联多个子表/外键（比如Parent表对应Child1的两个外键）时，创建第二个触发器会直接报错。
• 触发器命名引入自增变量@count，导致同一父表生成多个独立触发器，无法实现“单个触发器整合所有关联删除逻辑”的需求。

解决方案：按父表分组创建单个触发器 #

我们需要按父表分组，为每个父表仅创建一个触发器，将所有关联的子表删除逻辑整合到该触发器中。以下是修改后的动态创建脚本：

DECLARE @ParentTable NVARCHAR(128)
DECLARE @TriggerSQL NVARCHAR(MAX)
DECLARE @DeleteStatements NVARCHAR(MAX)
DECLARE @PKColumn NVARCHAR(128)

-- 游标遍历所有唯一的父表
DECLARE ParentCursor CURSOR FOR
SELECT DISTINCT parenttable 
FROM dbo.LF_DB_Relations

OPEN ParentCursor
FETCH NEXT FROM ParentCursor INTO @ParentTable

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 初始化变量
    SET @DeleteStatements = ''
    -- 获取当前父表的主键字段
    SELECT TOP 1 @PKColumn = QUOTENAME(primarykeycolumn)
    FROM dbo.LF_DB_Relations
    WHERE parenttable = @ParentTable

    -- 收集当前父表对应的所有子表删除逻辑
    SELECT @DeleteStatements = @DeleteStatements + 
        'DELETE FROM ' + QUOTENAME(childtable) + 
        ' WHERE ' + QUOTENAME(foreignkeycolumn) + ' IN (SELECT ' + @PKColumn + ' FROM DELETED);' + CHAR(13) + CHAR(10)
    FROM dbo.LF_DB_Relations
    WHERE parenttable = @ParentTable

    -- 拼接完整的触发器SQL
    SET @TriggerSQL = N'
CREATE OR ALTER TRIGGER Delete_' + @ParentTable + '_Purging
ON ' + QUOTENAME(@ParentTable) + '
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 删除所有关联子表记录
    ' + @DeleteStatements + '
    -- 删除父表本身的记录
    DELETE FROM ' + QUOTENAME(@ParentTable) + '
    WHERE ' + @PKColumn + ' IN (SELECT ' + @PKColumn + ' FROM DELETED);
END'

    -- 执行创建触发器的SQL
    EXEC sp_executesql @TriggerSQL

    PRINT '触发器 Delete_' + @ParentTable + '_Purging 已创建/更新'

    FETCH NEXT FROM ParentCursor INTO @ParentTable
END

CLOSE ParentCursor
DEALLOCATE ParentCursor

脚本说明 #

1. 使用游标遍历所有唯一的父表，确保每个父表仅处理一次。
2. 对每个父表，拼接所有关联子表的DELETE语句，把所有外键匹配的删除逻辑整合到一起。
3. 使用CREATE OR ALTER TRIGGER（SQL Server 2016及以上支持），避免重复创建报错，同时支持后续更新触发器逻辑。
4. 使用QUOTENAME()函数处理表名和字段名，防止特殊字符或关键字导致语法错误。
5. 最后执行父表自身的删除操作，完成级联删除逻辑。

内容的提问来源于stack exchange，提问作者Anand Mohan Singh