Cannot use text, ntext, or image columns in the &#

问题:

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?

回答1:

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.

回答2:

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.

回答3:

Using "INSTEAD OF " triggers may solve the problem because the text, ntext and image fields are available in the "inserted" and "deleted" tables. Look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148387 to see how it can be done.

回答4:

Use the following trigger after insert on yourtable:

SELECT textfields
FROM yourtable
WHERE EXISTS (
  SELECT 1
  FROM {inserted/deleted}
  WHERE {inserted/deleted}.PK = {yourtable}.PK
)