0

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 Aug 16 '16 at 20:06
  • Text :-) Free-form user notes. – Bill Wallace Aug 16 '16 at 20:08
  • You are using the wrong data type. Try varchar(max). – datagod Aug 16 '16 at 20:10
  • I am not using the wrong data type, the vendor is; unfortunately I have no control over it. – Bill Wallace Aug 16 '16 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? – Solomon Rutzky Aug 16 '16 at 21:17
0

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
AS 
BEGIN

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

END
GO


update dbo.textdatatypetest set col1='oneforalx'
| improve this answer | |
  • 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. – Bill Wallace Aug 16 '16 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 – Scott Hodgin Aug 16 '16 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. – Bill Wallace Aug 16 '16 at 21:29
  • My testing was done on sql 2014 sp2 – Scott Hodgin Aug 16 '16 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 – Bill Wallace Aug 17 '16 at 13:08
0

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
go
-- 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); 
go

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

if object_id('dbo.upd_trg', 'TR') is not null
    drop trigger upd_trg;
go
create trigger upd_trg on dbo.t
instead of update
as
begin
  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)
  begin
      -- you can put your own business logic here, such as log the before/after update into a separate table
      print 'column [a] changed'; 
  end
  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 

end
go

-- 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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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