?
Solved

SQL trigger and DataType "text"

Posted on 2012-08-15
3
Medium Priority
?
527 Views
Last Modified: 2012-08-15
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?
0
Comment
Question by:webressurs
3 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 38295494
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
0
 
LVL 1

Author Comment

by:webressurs
ID: 38295515
If I do like this I get the same error message:

select @info = info from INSERTED
0
 
LVL 11

Accepted Solution

by:
Lara F earned 2000 total points
ID: 38296031
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question