We help IT Professionals succeed at work.

SQL trigger and DataType "text"

I have a table (tblInfo) with a field called "info". This field has DataType = text.
The table has a SQL Trigger that needs to do a select from the "info" field. Because of the DataType (text) I get this error:


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


Here is my code:

ALTER TRIGGER [dbo].[trgHandler]
   ON  [dbo].[tblInfo]
   for insert
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @msg varchar(4)
      DECLARE @info varchar(max)

      SET @msg = (select msg from INSERTED)
      SET @info = (select info from INSERTED)  -- THIS FAILS!

      IF (@msg = '1234')
      BEGIN
        -- Do something with @info here...
      END
END


My question is: How can I get the text from the "info" field in my SQL trigger?
Comment
Watch Question

Peter ChanProblem resolver

Commented:
Try
ALTER TRIGGER [dbo].[trgHandler]
   ON  [dbo].[tblInfo]
   for insert
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @msg varchar(4)
      DECLARE @info varchar(max)

      select @msg = msg,@info =info from INSERTED

      IF (@msg = '1234')
      BEGIN
        -- Do something with @info here...
      END
END

Author

Commented:
If I do like this I get the same error message:

select @info = info from INSERTED
EA
CERTIFIED EXPERT
Commented:
Take a look on question.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26392468.html
main idea is to read text field from original table, not from inserted/deleted

Explore More ContentExplore courses, solutions, and other research materials related to this topic.