无法使用触发器在动态SQL中绑定多部分标识符

Question

    CREATE TRIGGER TestDemo ON Consultants1 
    INSTEAD OF UPDATE
    AS
    DECLARE @Sql nvarchar(200),     
            @TableName nvarchar(50),
            @FieldName nvarchar(100),
            @PKCols VARCHAR(1000),  --Primary Key       
            @Value nvarchar(100)

    SET @TableName = 'Consultants1'
    SET @FieldName = 'DisplayName'
    SET @Value = 'Test123'

    SELECT * INTO #ins FROM inserted

    SELECT @PKCols = c.COLUMN_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE   pk.TABLE_NAME = @TableName
        AND CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND c.TABLE_NAME = pk.TABLE_NAME
        AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    SELECT @Sql = 'UPDATE ' + @TableName + ' SET '
    SELECT @Sql = @Sql + @FieldName + ' = ''' + @Value + ''''
    SELECT @Sql = @Sql + ' WHERE #ins.' + @PKCols + ' = ' + @TableName + '.' + @PKCols
    EXEC (@Sql)

    UPDATE Consultants1 SET DisplayName = 'abcd'
    where ConsIntID = 'Test123285'

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "#ins.ConsIntID" could not be bound.

Answer 1

您可以更改这行代码

SELECT @Sql = @Sql + ' WHERE #ins.' + @PKCols + ' = ' + @TableName + '.' + @PKCols

对此

SELECT @Sql = @Sql + ' FROM #ins a INNER JOIN ' + @TableName + ' b ON a.' + @PKCols + ' = b.' + @PKCols

另一种情况是，在更改之后，您可能会收到一个错误：Msg 570,Level 16,State 1,Line 1代替触发器不支持直接递归。触发器执行失败。