The multi-part identifier could not be bound in Dynamic SQL using Trigger

Question

Following is a part of my instead of update trigger

    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)

The trigger gets created without any errors. Now I am trying to execute the following query

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

It gives a error saying:

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

I could not understand where my code goes wrong. Please help

Answer 1

You may change this line of code

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

to this

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

Another story is that after the change you'll probably receive an error: Msg 570, Level 16, State 1, Line 1 INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

Here's a good article about INSTEAD OF triggers and recursion in MSDN. Check the Remarks section.