"Raymond" <raymond.entena@bakernet.com> wrote in message
news:33e601c3acde$97f18190$3101280a@phx.gbl...
> Hi All,
>
> I'm trying to create an update and delete trigger LOGS but
> I'm receiving this error:
> "Error 311:Cannot use text, ntext, or image columns in
> the 'inserted' and 'deleted' tables."
>
> The source table has "TEXT" data type columns. Is there a
> workaround for this? I tried using CAST on the affected
> columns but I'm still getting the same error.
>
> I can't do this from within the front-end level because
> I'm using a package software so the best way is to track
> it through backend(triggers).
>
> Any help is very much appreciated.
>
> Thanks,
> Ray
>
>
>

"Steve Kass" <skass@drew.edu> wrote in message
news:edyumLOrDHA.1408@TK2MSFTNGP11.phx.gbl...
> Not certain, but I think the excerpt you gave should say "is equal to 70
> or higher." Here are a couple more excerpts.
>
>
> SQL Server 2000 does not allow text, ntext, or image column references
> in the inserted and deleted tables for AFTER triggers; however, these
> column references are allowed for INSTEAD OF triggers. For more
> information, see CREATE TRIGGER.
>
>
> 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.
>
> SK
>
> oj wrote:
>
> >Ray,
> >
> >Here is an excerpt from bol:
> >
> >In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
> >ntext, or image column references in the inserted and deleted tables
> if the
> >compatibility level is equal to 70. The text, ntext, and image values
> in the
> >inserted and deleted tables cannot be accessed. To retrieve the new
> value in
> >either an INSERT or UPDATE trigger, join the inserted table with the
> >original update table. When the compatibility level is 65 or lower, null
> >values are returned for inserted or deleted text, ntext, or image
columns
> >that allow null values; zero-length strings are returned if the
> columns are
> >not nullable.
> >
>

oj wrote:

>Ray,
>
>Here is an excerpt from bol:
>
>In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text,
>ntext, or image column references in the inserted and deleted tables
if the
>compatibility level is equal to 70. The text, ntext, and image values
in the
>inserted and deleted tables cannot be accessed. To retrieve the new
value in
>either an INSERT or UPDATE trigger, join the inserted table with the
>original update table. When the compatibility level is 65 or lower, null
>values are returned for inserted or deleted text, ntext, or image columns
>that allow null values; zero-length strings are returned if the
columns are
>not nullable.
>