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

Question

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

What should be the workaround in this case? :(

Answer 1

As of SQL Server 2005, TEXT/NTEXT/IMAGE are deprecated - you should use the (N)VARCHAR(MAX) and VARBINARY(MAX) data types instead.

(N)VARCHAR(MAX) (see MSDN docs here) and VARBINARY(MAX) allow up to 2 GByte of data

From the MSDN docs:

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. (= 2 GB)

The (N)VARCHAR(MAX) types also allow all the usual T-SQL string function to work on them - something that wasn't the case with (N)TEXT at all.

As this MSDN article shows, the replacement types are supported in triggers, too:

SQL Server 2008 does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables.

Answer 2

This is the workaround:

SQL Trigger cannot do INSTEAD OF DELETE but is required for ntext, image columns

Essentially you need to join the inserted and/or deleted pseudo-tables onto the underlying table and read the NTEXT, TEXT or IMAGE data from the underlying tables.