SQL Server After Insert/Update/Delete触发器数据类型错误求解

根因说明 #

报错由SQL隐式类型转换触发，核心触发场景如下：

• 审计表[Audit].[dbo].[PriceAudit]的PK、OldValue、NewValue字段为int类型，无法存储带小数点的数值内容
• 代码中重复为@PKSelect赋值，覆盖了多主键拼接逻辑，若主表单列主键为decimal/float类型，插入int类型的审计表PK字段时直接触发转换失败
• WHERE子句中直接比较不同类型的字段值，SQL会自动尝试将字符串转成数值进行比较，格式不符就抛出错误

修复步骤 #

• 第一步：调整审计表字段类型
  确认PriceAudit表的PK、OldValue、NewValue字段均为varchar(1000)或nvarchar(1000)类型，禁止设置为int、bigint等数值类型。

• 第二步：删除重复的@PKSelect赋值逻辑
  移除代码中第二段@PKSelect赋值的代码，仅保留第一段多主键拼接逻辑：

  -- 保留这段代码
  SELECT @PKSelect = COALESCE(@PKSelect + '+', '') 
         + '''<[' + COLUMN_NAME 
         + ']=''+convert(varchar(100),
  coalesce(i.[' + COLUMN_NAME + '],d.[' + 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 @PKSelect = 'convert(varchar(100),
  coalesce(i.[' + COLUMN_NAME + '],d.[' + 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
  */
  

• 第三步：修复@AuditGroupPK的计算和拼接逻辑
  避免审计表为空时返回NULL，同时如果GroupId字段为字符串类型，拼接时增加单引号包裹：

  -- 替换原来的@AuditGroupPK赋值逻辑
  SELECT @AuditGroupPK = ISNULL((SELECT top 1 Id FROM [Audit].[dbo].[PriceAudit] Order by Id desc), 0) + 1
  
  -- 如果GroupId是varchar类型，替换原来的拼接逻辑
  -- 原逻辑：+ ',' + @AuditGroupPK + ''
  -- 新逻辑：+ ',''' + @AuditGroupPK + ''''
  

• 第四步：调整WHERE条件避免隐式转换
  将字段值转为字符串后再进行比较，避免触发自动类型转换：

  -- 替换原来的WHERE条件
  + ' where CAST(CAST(i.' + @fieldname + ' AS nvarchar(1000)) AS varchar(1000)) <> CAST(CAST(d.' + @fieldname + ' AS nvarchar(1000)) AS varchar(1000))'
  + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
  + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)'
  

内容的提问来源于stack exchange，提问作者jimmy