title: Trigger functions with other tables, but not this one. Msg 102
body: <p>I'm pulling over from a previous post of mine, <a href="https://stackoverflow.com/questions/63192581/mssql-msg-102-and-column-name-begins-with-number/63340839#63340839">MSSQL Msg 102 &#39;.&#39; and column name begins with number</a>.</p>
  <p>I keep getting a Msg 102 incorrect syntax within a stored procedure, when I try to run an UPDATE.</p>
  <p>OK, I think I have found where the issue is at.  It is in a TRIGGER, for insert, update, delete:</p>
  <pre><code>DECLARE
  @bit INT ,
  @field INT ,
  @maxfield INT ,
  @char INT ,
  @fieldname VARCHAR(64) ,
  @TableName VARCHAR(64) ,
  @PKCols VARCHAR(1000) ,
  @sql VARCHAR(2000) ,
  @UpdatedDate VARCHAR(21) ,
  @UserName VARCHAR(64) ,
  @TYPE CHAR(1) ,
  @PKSelect VARCHAR(1000) ,
  @OldValueSQL VARCHAR(128) ,
  @NewValueSQL VARCHAR(128) ,
  @fieldtype VARCHAR(32) ,
  @compatible_triggerfield BIT
  
  select @TableName = 'tblItems'
  
  SELECT @UserName = dbo.fnGetContextInfoString() ,@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
  + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
  
  IF EXISTS (SELECT * FROM inserted)
  IF EXISTS (SELECT * FROM deleted)
  SELECT @Type = 'U'
  ELSE
  SELECT @Type = 'I'
  ELSE
  SELECT @Type = 'D'
  
  SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #ins FROM inserted
  SELECT [ID],[ResourceName],[DevItemNum],[Email],[ItemOrg],[ItemPOC],[24_7POC],[ItemDesc],[Active],[EntryArea],[Entry] INTO #del FROM deleted
  
  SELECT @PKCols = COALESCE(@PKCols + 'and', 'on')
    + ' i.' + cu.COLUMN_NAME + ' = d.' + cu.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    WHERE   pk.TABLE_NAME = @TableName
    AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
    AND     cu.TABLE_NAME = pk.TABLE_NAME
    AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  
  SELECT @PKSelect = COALESCE(@PKSelect+'+'.'')
    + '''&lt;' + COLUMN_NAME
    + '=''convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+''&gt;'''
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
    WHERE pk.TABLE_NAME = @TableName
    AND     CONSTRAINT_TYPE = 'PRIMARY_KEY'
    AND     cu.TABLE_NAME = pk.TABLE_NAME
    AND     cu.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  IF @PKCols IS NULL
  BEGIN
    RAISERROR('no PK on table %s', 16, -1, @TableName)
    RETURN
  END
  IF @Type = 'I' or @ Type = 'D'
  BEGIN
  SET @maxfield = 1
  SET @field = 0
  END
  ELSE
  BEGIN
  SELECT field = 0,
    @maxfield = MAX(ORDINAL_POSITION)
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
  END
  WHILE @field &lt; @maxfield
  BEGIN
  SELECT @field = MIN(ORDINAL_POSITION)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    AND ORDINAL_POSITION &gt; @field
  SELECT @bit = (@field -1)% 8 + 1
  SELECT @bit = POWER(2,@bit -1)
  SELECT @char = ((@field - 1) / 8) + 1
  IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) &amp; @bit &gt; 0
    OR @Type IN ('I','D')
  BEGIN
  SELECT @fieldname = COLUMN_NAME, @fieldtype = DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    AND ORDINAL_POSITION = @field
  IF @fieldtype = 'text' or @fieldtype = 'ntext' or @fieldtype = 'image' SET @compatible_triggerfield = 0 else SET @compatible_triggerfield = 1
  SET @OldValueSQL = ',convert(varchar(1000),d.' + @fieldname + ')'
  SET @NewValueSQL = ',convert(varchar(1000),i.' + @fieldname + ')'
  IF @compatible_triggerfield = 0
  SET @OldValueSQL = ',''n/a'''
  SET @NewValueSQL = ',''n/a'''
  END
  SELECT @sql = '
  insert Audit (    Type,
    TableName,
    PrimaryKeyField,
    FieldName,
    FieldType,
    OldValue,
    NewValue,
    UpdateDate,
    UserName)
  select ''' + @Type + ''','''
  + @TableName + ''',' + @PKSelect
  + ',''' + @fieldname + ''''
  + ',''' + @fieldtype + ''''
  + @OldValueSQL
  + @NewValueSQL
  + ',''' + @UpdateDate + ''''
  + ',''' + @UserName + ''''
  + ' from #ins i full outer join #del d'
  + @PKCols
  IF @compatible_fieldtrigger = 1
  BEGIN
    SET @sql = @sql
    + ' where i.' + @fieldname + '&lt;&gt; d.' + @fieldname
    + ' or (i.' + @fieldname + ' is null and  d.'
  + @fieldname
  + ' is not null)'
    + ' or (i.' + @fieldname + ' is not null and d.'
  + @fieldname
  + ' is null)'
  END
  EXEC(@sql)
  END
  END
  </code></pre>
  <p>Here's the kicker.  If I comment out the <code>SELECT @PKSELECT = ...</code> statement, it seems to run fine.</p>
  <p>Anymore help here, would be nice.</p>