SQL Server 2012-触发:操作数类型冲突:INT与唯一标识符不相容

Question

我正在编写一个审核触发器，该触发触发器保存了另一个表中的表中发生的每个更改。但是当我尝试更改Users表时，我会收到此错误：

操作数类型冲突：int与唯一标识符不相容

Users表包含一个独特的身份列，以及其他列。

这是发生错误发生的代码的一部分：

ALTER TRIGGER [dbo].[UsersLogger] 
ON [dbo].[Users] 
FOR INSERT, DELETE, UPDATE
AS 
BEGIN
    DECLARE @audit_oldvalue sql_variant;
    DECLARE @audit_value sql_variant;
    DECLARE @audit_field varchar(100);
    DECLARE @sql nvarchar(max);
    DECLARE @ParmDefinition nvarchar(max) ;
    DECLARE @OutString varchar(max) ;
    DECLARE @converted_uid nvarchar(50);
    DECLARE @qid int ;
    DECLARE @Cinfo VARBINARY(128)  
    SELECT @Cinfo = Context_Info()  
    IF @Cinfo = 0x55555  
         RETURN  
    DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)
    SET @OutString = '' 
    SELECT * 
    INTO #tempTrigT 
    FROM 
        (SELECT * 
         FROM deleted 
         WHERE @Action IN ( 'U', 'D')) A 
    UNION 
    (SELECT * 
     FROM inserted 
     WHERE @Action = 'I') 
    SET @sql = '' 
if @Action = 'U'
BEGIN
Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name +  
'],0) = IsNull(d.[' + Column_name + '],0) then ''''  
 else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +' 
from information_schema.columns  
where table_name = 'Users' and column_name <>'rowguid' and column_name <>'modifieddate'
set @ParmDefinition = '@OutString varchar(max) OUTPUT' 
set @sql = 'Select @OutString = '  
+ Substring(@sql,1 , len(@sql) -1) +  
' From dbo.Users i  ' 
+ ' inner join #tempTrigT d on 
i.id = d.id'  
exec sp_executesql @sql, @ParmDefinition, @OutString OUT 
END
 DECLARE @Items VARCHAR(max)
 set @Items = @OutString;
 DECLARE @Item VARCHAR(50)
 DECLARE @Pos INT
 DECLARE @Loop BIT
 SELECT @Loop = CASE WHEN LEN(@OutString) > 0 THEN 1 ELSE 0 END
 WHILE (SELECT @Loop) = 1
 BEGIN
 SELECT @Pos = CHARINDEX(',', @OutString, 1)
 IF @Pos > 0
 BEGIN
 SELECT @Item = SUBSTRING(@OutString, 1, @Pos - 1)
 SELECT @OutString = SUBSTRING(@OutString, @Pos + 1, LEN(@OutString) - @Pos)
IF (TRY_CONVERT(UNIQUEIDENTIFIER, @Item) is not null)
begin
    select @Item = convert(nvarchar(50), @Item)
end

Answer 1

如果我不得不猜测，我会把它归咎于

Select @sql = @sql + 'Case when IsNull(i.[' + Column_Name +  
'],0) = IsNull(d.[' + Column_name + '],0) then ''''  
 else ' + quotename(Column_Name, char(39)) + ' + '',''' + ' end +' 
from information_schema.columns  
where table_name = 'Users' and column_name <>'rowguid' and column_name <>'modifieddate'

这是因为您在UNIQUEIDENTIIFIER列上进行ISNULL并将值设置为int。您可以从ISNULL(i.[ColumnName], 0)更改为ISNULL(i.[ColumnName], '')，但是，假设表中的所有列都是字符串适用的数据类型。否则，您可以使用该表中的[DATA_TYPE]列对ISNULL函数的替换值进行一些有条件的逻辑。