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

Question

Hello,

I got an error "Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables", since there are ntext fields in original table, I had attached a trigger to.

Here is a solution: http://lazycodeprogrammer.blogspot.com/2009/08/how-to-use-inserteddeleted-with.html

But original (non modified) query is complex enough. What should I write instead of SELECT * FROM INSERTED, using JOIN operator, as it's recommended?

Answer 1

A nice solution has been found:

1. SELECT FROM INSERTED just id column (it's not ntext or image and query is being executed).
2. SELECT from original table * with the same ids.
3. If required, use UPDATED() on INSERTED to be aware, what columns have been changed.

Answer 2

The real problem is you are trying to select columns in your inserted table that are of type ntext, text or image. This is not allowed in a trigger.

The real solution would be to change all your ntext, to nvarchar(max), text to varchar(max) and image to varbinary(max) as suggested by MS.

MS states that those types are deprecated and will be removed in future version.

Also, ntext are really slow as performance since there are no in-row data.