Using text, ntext, and image Data in INSTEAD OF Triggers
Data modifications may involve text, ntext, and image columns. In base tables, the value stored in a text, ntext, or image column is a text pointer that points to the pages holding the data. For more information, see In-Row Data.
Note
Using text, ntext, and image data is available 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 using varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables.
Although AFTER triggers do not support text, ntext, or image data in the inserted and deleted tables, INSTEAD OF triggers do support them. text, ntext, and image data is stored in the inserted and deleted tables differently from the way the data is stored in base tables. text, ntext, and image data is not stored as a separate chain of pages. Instead, they are stored as a continuous string within each row. This means there are no text pointers for text, ntext, or image columns in the inserted and deleted tables. The TEXTPTR and TEXTVALID functions and the READTEXT, UPDATETEXT, and WRITETEXT statements are not valid against text, ntext, or image columns from the inserted or deleted tables. All other uses of text, ntext, or image columns are supported, such as referring to them in select lists, WHERE clause search conditions, or the SUBSTRING, PATINDEX, or CHARINDEX functions. Operations on text, ntext, or image data in the INSTEAD OF triggers are affected by the current SET TEXTSIZE option. This value can be determined by using the @@TEXTSIZE function.
The type of text, ntext, or image data stored in the inserted and deleted tables varies depending on the triggering action (INSERT, UPDATE, or DELETE):
On INSERT statements, the inserted table contains the new value for the text, ntext, or image column. The deleted table has no rows.
On DELETE statements, the inserted table has no rows and the deleted table rows contain the values the text, ntext, or image column had before the DELETE started.
On UPDATE statements in which the text, ntext, or image value is not changed, both the inserted and deleted table rows contain the same values for the text, ntext, or image column.
On UPDATE statements in which the text, ntext, or image value is changed, the deleted table contains the data values as they existed before the UPDATE started, and the inserted table contains the data with any modifications specified in the SET clause.
If an INSERT, UPDATE, or DELETE statement modifies many rows with large text, ntext, or image values, lots of memory can be required to hold the copies of the text, ntext, or image data in the inserted and deleted tables. Copying large amounts of data can also reduce performance. INSERT, UPDATE, and DELETE statements that reference views or tables that have INSTEAD OF triggers should modify one row at a time, or only several rows at a time, whenever possible.