Using SQL Server 2012, how can I check for changes in a text column?

I know the text datatype has been deprecated, and get the ever helpful message

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

when trying to reference the existing text column in a trigger, so things like joining inserted & deleted tables on the column does not work. UPDATE() would work, except it is always true (I am guessing the front end just pushes whatever data is in the form field regardless if it changed or not).

Any suggestions on how I can check for changes on the column?

  • What kind of data are you storing in this column?
    – datagod
    Commented Aug 16, 2016 at 20:06
  • Text :-) Free-form user notes. Commented Aug 16, 2016 at 20:08
  • You are using the wrong data type. Try varchar(max).
    – datagod
    Commented Aug 16, 2016 at 20:10
  • I am not using the wrong data type, the vendor is; unfortunately I have no control over it. Commented Aug 16, 2016 at 20:16
  • Have you looked into Change Data Capture? This page, Track Data Changes does not indicate that the TEXT datatype would be a problem. Or, perhaps create an Indexed View that selects the PK column(s) and then CONVERT(VARCHAR(MAX), tbl.UserNotes), and then create an AFTER UPDATE Trigger on the Indexed View? Commented Aug 16, 2016 at 21:17

2 Answers 2


Can you implement an INSTEAD OF UPDATE trigger? - a quick test on my local instances indicates you can reference the inserted and deleted tables. Naturally, you still have to apply the real update.

here was my quick test

create table TextDataTypeTest (Col1 text)
insert into dbo.textdatatypetest (col1) values('test')
create TRIGGER dbo.TextDataTypeTest_Trigger
   ON  dbo.TextDataTypeTest 
   instead of UPDATE

select col1 into #Before from deleted
select col1 into #After from inserted

select * from #Before

select * from #After
update a 
set a.col1=b.col1 
from dbo.textdatatypetest a join
inserted b on 1=1


update dbo.textdatatypetest set col1='oneforalx'
  • That's what I'm leaning towards, but I still cannot access the text data-type columns in the i & d tables. I am thinking with an INSTEAD OF UPDATE trigger I can pull the original value, proceed with the update, pull the new value, and then do the compare. This is the approach I will try next. Commented Aug 16, 2016 at 21:01
  • Hmm, perhaps i'm missing something, but i didn't have any problems accessing the text column from the i & d tables. I updated my answer with my very quick prototype, but again, maybe i'm missing something Commented Aug 16, 2016 at 21:10
  • I will check this later, but I am WAGing that you a using an older version of SqlServer from before depreciation of the text datatype. Commented Aug 16, 2016 at 21:29
  • My testing was done on sql 2014 sp2 Commented Aug 16, 2016 at 21:51
  • Y'all right, text columns can be accessed in INSTEAD OF triggers. technet.microsoft.com/en-us/library/ms175196(v=sql.105).aspx Commented Aug 17, 2016 at 13:08

Just as @Scott Hodgin mentioned, you can actually use INSTEAD OF trigger to work around your requirement.

Here is a more detailed solution template for your reference (details are explained in the comments)

use tempdb
-- create a sample table
if object_id('dbo.t', 'U') is not null
    drop table dbo.t;

-- we assume this table has a PK, so we can utilize this PK column(s) in the trigger
create table dbo.t (id int identity primary key, a text, b int); 

-- insert initial record
insert into dbo.t (a, b) values ('hello world', 10);

if object_id('dbo.upd_trg', 'TR') is not null
    drop trigger upd_trg;
create trigger upd_trg on dbo.t
instead of update
  set nocount on;
  declare @d table (id int primary key, a varchar(max), b int); -- store deleted table, change [a] column from text data type to varchar(max)
  declare @i table (id int primary key, a varchar(max), b int); -- store inserted table, 

  insert into @d (id, a, b) select id, a, b from deleted;
  insert into @i (id, a, b) select id, a, b from inserted;

  if exists (select * from @i i inner join @d d on i.id = d.id and i.a <> d.a)
      -- you can put your own business logic here, such as log the before/after update into a separate table
      print 'column [a] changed'; 
  update t set t.a = i.a, t.b =i.b  -- now cntinue to do the update
  from dbo.t t
  inner join @i i
  on i.id = t.id 


-- an example here
-- when we update column [a] with different value, a message "column [a] changed" will be printed out
update dbo.t set a='good morning', b = b+b
from dbo.t
where id = 1;

-- check it, [a] should be 'good morning' instead of the original 'hello world', and [b]=20 instead of original 10
select * from dbo.t where id =1;

-- when we do NOT update column [a] but other columns like [b], NO message will be printed out
update dbo.t set  b = b+b
from dbo.t
where id = 1;

-- check it, [a] should be still 'good morning', while [b] = 40 instead of 20 
select * from dbo.t where id =1;

This is tested in SQL Server 2012 version 11.0.5623.0 (x64)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.