SQL Server 2012 - Trigger: Operand type clash: int is incompatible with unique identifier

Question

I am writing an audit trigger which saves every change occurred in the table in another table. But I receive this error when I am trying to alter the Users table:

Operand type clash: int is incompatible with unique identifier

The Users table contains a uniqueidentifier column, among others.

Here is the part of the code where the error occurs:

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

dont know... most probably when the sql is executed but im not sure — aggicd
and you have all this in a trigger :O and its only a part of your trigger :O do you not like the application you are working on??? — M.Ali
@M.Ali its not mine and I had to work it like this... didnt have an option — aggicd

PreQL PreQL · Accepted Answer · 2017-09-05T00:05:23

If I had to guess, I'd blame it on

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'

It's because you're doing an ISNULL on a UNIQUEIDENTIIFIER column and setting the value to an INT. You could change from ISNULL(i.[ColumnName], 0) to ISNULL(i.[ColumnName], '') however, that is assuming all the columns in your tables are string applicable datatypes. Otherwise, you could do some conditional logic on the replacement value for the ISNULL function by using the [DATA_TYPE] column in that table.

SQL Server 2012 - Trigger: Operand type clash: int is incompatible with unique identifier

1 Answers