sql-server - 避免使用动态 SQL 在 UPDATE 触发器中递归

Question

我们正在构建一个受保护的 INSTEAD OF UPDATE 触发器来监控和控制几个表的更新（系统的大部分 MasterData 表，其中大约 150 个）。因此，尽可能（安装、更新）我们尝试使代码尽可能可重用（没有硬编码字段名称或表名称）。

为了控制行的“实际”版本，存在一个 _ACTIVE 字段，并且每个新版本都会减少（ACTIVE 行获得 ACTIVE = 1）。抱歉，由于向后兼容性，我们无法使用时态表功能（大量业务逻辑是基于此功能构建的）

更新逻辑包括在影响表之前处理 OLD 和 NEW 行，一旦处理完所有内容，更新表；不仅受影响的行，而且都具有相同的唯一性关键字段（唯一性字段的识别也旨在动态完成；在以下示例中，where 子句在变量 @toWhereOnClause 上动态构建）

“真实”表有两个动作，首先插入一堆新行，_ACTIVE = 2，其次，所有需要更新的行都得到 _ACTIVE -= 1，将最新版本的行设置为 1

出现问题是因为需要动态创建第二个操作，即更新，以避免输入表名，并手动设置 @toWhereOnClause。这会再次触发 TRIGGER，并且因为它是动态 SQL（我们相信）不会被 TRIGGER_NESTLEVEL() = 1 捕获

代码结构如下：

CREATE OR ALTER TRIGGER  [schema].[triggerName]  ON [schema].table
INSTEAD OF UPDATE
AS
BEGIN


SET @tableName          = '[schema].[table]'  // only line to modify for diferent tables


//TRIGGER PREPARATION

SET @schema             = (SELECT SUBSTRING(@tableName, 1, (CHARINDEX('].', @tableName))))
SET @table              = (SELECT SUBSTRING(@tableName, (CHARINDEX('[', @tableName, 3)), LEN(@tableName)))
SET @fieldNameS         = (SELECT + ',' + QUOTENAME(COLUMN_NAME)
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_SCHEMA = @schema            
                            AND TABLE_NAME = @table                     
                            ORDER BY ORDINAL_POSITION
                            FOR XML path(''));

SET @uniqueFieldSelector = (SELECT +' AND LeftTable.'+ COLUMN_NAME + ' = #INSERTED.' + COLUMN_NAME
                            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
                            WHERE TABLE_NAME = @tableName 
                            AND COLUMN_NAME NOT LIKE '%Active%' 
                            FOR XML PATH(''))

SET @toWhereOnClause = (SELECT (SUBSTRING(@uniqueFieldSelector, 5, LEN(@uniqueFieldSelector))))


// DUPLICATE TRIGGER TABLES INTO TEMP TABLE TO WORK ON NEW AND OLD LINES

SELECT * INTO #INSERTED FROM INSERTED -- Can't modify logic table values (INSERTED), we put it in a temp table
SELECT * INTO #DELETED FROM DELETED

// SEVERAL INSTRUCTIONS TO TREAT THE OLD AND NEW LINES (not shown here) AND CALCULATE IF THE UPDATE IS LEGAL (@CONTINUE_TRIGGER)
...

// REAL UPDATE
IF TRIGGER_NESTLEVEL() = 1 AND @CONTINUE_TRIGGER = TRUE
        --https://stackoverflow.com/questions/1529412/how-do-i-prevent-a-database-trigger-from-recursing  

    BEGIN
        SET @statementINSERT = N'INSERT INTO' +  @tableName + '( ... ) 
                            SELECT   ...  FROM #INSERTED ';                        

        EXECUTE sp_executesql @statementINSERT



        SET @statementUPDATE = N'UPDATE TheRealTable
                SET TheRealTable._ACTIVE -= 1
                FROM ' + @tableName + ' AS TheRealTable
                INNER JOIN #INSERTED ON ' + @toWhereOnClause;

        EXECUTE sp_executesql @statementUPDATE   


    END


END 

是的，我们知道它很复杂，但传统并没有提供太多选择。

所以：

有什么办法可以避免再次触发动态SQL 触发器？

（系统运行在 WindowsServer 和 Azure 实例上，至少兼容 120）

标签： sql-serverazuretriggersazure-sql-databasedynamic-sql