[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

problem with nText Datatype when firing a Instead of Trigger

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.
0
SwamyN
Asked:
SwamyN
1 Solution
 
appariCommented:

you cannot use text/image data types cannot be compared or sorted.
if you have to do the comparisions you can convert them to varchar type and do comparision. but that may result in truncation so the comparision may not be correct. other option is if the datatype is text always insert in to your audit table.

for the first option you can try something like this
change following

select @sql = @sql +       ' where i.' + @fieldname + ' <> d.' + @fieldname

to

select @sql = @sql +       ' where convert(varchar(8000), i.' + @fieldname + ') <> convert(varchar(8000), d.' + @fieldname + ')'
0
 
adatheladCommented:
As appari said, you need to convert the ntext datatype - the only tweak I'd make is to convert it to NVARCHAR(4000) instead of VARCHAR(8000) seeing as you have an NTEXT column. Does mean you're restring it to 4000 chars for the purpose of the WHERE condition but keep the unicode support.
0
 
SwamyNAuthor Commented:
Thanx Appari,
I got the solution to insert records in Audit trail. it can be done by calling the separate trigger for Insertion / deletion and updation but I am not able to update the original table on which trigger is called.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now