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 pointing to the pages holding the data. For more information, see text, ntext, and image Data.

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, which 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, which can be determined with 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, considerable memory can be required to hold the copies of the text, ntext, or image data in the inserted and deleted tables. Copying these large amounts of data can also lower 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 a few rows at a time, whenever possible.