troubleshooting Question

problem with nText Datatype when firing a Instead of Trigger

Avatar of SwamyN
SwamyN asked on
Microsoft SQL ServerWindows OS
3 Comments1 Solution788 ViewsLast Modified:
I got the error while fire a trigger :
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
'------------------------------


How can I trace the Insertion/Updatation/Deletion through a Trigger(Instead of) . My table contains nText dataType and For and After Trigger does not entertain the said Datatype.
I Have a trigger that dymanically get the value insterted/Updated and Deleted from the Table and insert into the Audit Table.
Code of Trigger is as below :
CREATE trigger tr_Emp_test_ins on Emp_test  instead of  insert
as

declare @bit int ,
      @field int ,
      @maxfield int ,
      @char int ,
      @fieldname varchar(128) ,
      @TableName varchar(128) ,
      @PKCols varchar(1000) ,
      @sql varchar(2000),
      @UpdateDate varchar(21) ,
      @UserName varchar(128) ,
      @Type char(1) ,
      @PKSelect varchar(1000)
      
      select @TableName = 'Emp_test'

      -- date and user
      select       @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
      select @UserName='pmm1'
      
      -- Action
      select @Type = 'I'
      
      
      -- get list of columns
      select * into #ins from inserted
      select * into #del from deleted
      
      -- Get primary key columns for full outer join
      select      @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.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
      
      -- Get primary key select for insert
      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
      
      --Commented By Rupendra, PKCols can be Null
      --if @PKCols is null
      --begin
            --raiserror('no PK on table %s', 16, -1, @TableName)
            --return
      --end
      
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
      while @field < @maxfield
      begin
            select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @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) & @bit > 0 or @Type in ('I','D')
            begin
                  select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
                  select @sql =             'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
                  select @sql = @sql +       ' select ''' + @Type + ''''
                  select @sql = @sql +       ',''' + @TableName + ''''
                  select @sql = @sql +       ',' + @PKSelect
                  select @sql = @sql +       ',''' + @fieldname + ''''
                  select @sql = @sql +       ',convert(varchar(1000),d.' + @fieldname + ')'
                  select @sql = @sql +       ',convert(varchar(1000),i.' + @fieldname + ')'
                  select @sql = @sql +       ',''' + @UpdateDate + ''''
                  select @sql = @sql +       ',''' + @UserName + ''''
                  select @sql = @sql +       ' from #ins i full outer join #del d'
                  select @sql = @sql +       @PKCols
                  select @sql = @sql +       ' where i.' + @fieldname + ' <> d.' + @fieldname
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)'
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)'
                  exec (@sql)
            end
      end

It is working fine for other tables where datatype is not of nText/Text.Image...
Is there any Solution for it.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros