标准审计触发器报错：varchar转numeric数据类型转换失败

错误原因 #

生成的审计SQL中，针对数值类型字段JobHours使用了ISNULL(i.[JobHours], '')，SQL Server在比较数值与字符串时会隐式将字符串转换为数值类型，空字符串''无法转换为有效数值，因此抛出"Error converting data type varchar to numeric"错误。

另外你提供的生成SQL里还有一个语法笔误：i.JobHours]缺少左方括号，正确写法应为i.[JobHours]。

解决方案 #

方案1：通用型判断（推荐，适配所有数据类型） #

直接判断字段值不等或NULL状态不一致，完全避免类型转换问题。将触发器中生成WHERE子句的代码替换为：

+ ' where (i.[' + @fieldname + '] <> d.[' + @fieldname + '] OR (i.[' + @fieldname + '] IS NULL AND d.[' + @fieldname + '] IS NOT NULL) OR (i.[' + @fieldname + '] IS NOT NULL AND d.[' + @fieldname + '] IS NULL))'

方案2：按字段类型适配NULL替代值 #

针对不同数据类型使用对应类型的NULL替代值，避免隐式转换：
在循环中添加字段类型判断逻辑：

-- 获取当前字段的数据类型
DECLARE @dataType VARCHAR(128)
SELECT @dataType = DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName 
AND COLUMN_NAME = @fieldname

-- 根据数据类型设置NULL替代值
DECLARE @nullReplacement VARCHAR(100)
IF @dataType IN ('varchar', 'nvarchar', 'char', 'nchar')
    SET @nullReplacement = ''''''
ELSE IF @dataType IN ('int', 'bigint', 'smallint', 'tinyint', 'numeric', 'decimal', 'float', 'real')
    SET @nullReplacement = '0'
ELSE IF @dataType IN ('date', 'datetime', 'datetime2', 'smalldatetime')
    SET @nullReplacement = '''1900-01-01'''
ELSE
    SET @nullReplacement = 'NULL'

-- 生成WHERE子句
SELECT @sql = @sql + ' where ISNULL(i.[' + @fieldname + '],' + @nullReplacement + ') <> ISNULL(d.[' + @fieldname + '],' + @nullReplacement + ')'

修改后的触发器关键代码块（方案1示例） #

将原触发器中生成@sql的代码段替换为以下内容：

SELECT @fieldname = COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @TableName 
AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + @Tablename),COLUMN_NAME, 'ColumnID') = @field

SELECT @sql = 'insert ' +  @AuditTable + ' 
(Type, 
TableName, 
PK, 
FieldName, 
OldValue, 
NewValue, 
UpdateDate, 
UserName)

SELECT ''' + @Type + ''',''' 
+ @TableName + ''',' + @PKSelect
+ ',''[' + @fieldname + ']'''
+ ',convert(varchar(1000),d.[' + @fieldname + '])'
+ ',convert(varchar(1000),i.[' + @fieldname + '])'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where (i.[' + @fieldname + '] <> d.[' + @fieldname + '] OR (i.[' + @fieldname + '] IS NULL AND d.[' + @fieldname + '] IS NOT NULL) OR (i.[' + @fieldname + '] IS NOT NULL AND d.[' + @fieldname + '] IS NULL))'

EXEC (@sql)

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