asked on

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?

Peter Chan

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

webressurs

ASKER

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

select @info = info from INSERTED

ASKER CERTIFIED SOLUTION

Lara F

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SQL trigger and DataType &quot;text&quot;

SQL trigger and DataType "text"