Use 'text' data types in triggers?

This might get a little long, but I'll try to keep it short...

We are building a new tracking system where we have our tables of data, plus an additional table that logs everything done to it's counterpart (i.e. tbl_users also has a tbl_users_log that tracks all inserts, updates, & deletes)

Some of the tables are setup to use text fields so we can use full-text searching.  We also have triggers that auto-update the _log table when the original table is changed.

Here's my problem - I was just trying to write a trigger for a table with a text field in it, and I can't get the trigger to use the text fields.


I tried:

*************************************
CREATE TRIGGER [trig_Insert_Pagers_Users] ON dbo.Pagers_Users
FOR INSERT

AS

DECLARE @userId int,
      @firstName text,
      @lastName text,
      @pagerId int,
      @logBy varchar(10)

SELECT @userId = id, @firstName = firstName, @lastName = lastName, @pagerId = pagerId, @logBy = logBy FROM Inserted

-- Insert data
INSERT INTO Pagers_Users_Log
(dbAction, userId, newFirstName, newLastName, newPagerId, dateTime, logBy)
VALUES
('Insert', @userId, @firstName, @lastName, @pagerId, GETDATE(), @logBy)
*************************************

which gave me the error:

     "Error 2739: The text, ntext, and image data types are invalid for local variables."

SO I thought maybe SQL would convert them to varchars...

*************************************
CREATE TRIGGER [trig_Insert_Pagers_Users] ON dbo.Pagers_Users
FOR INSERT

AS

DECLARE @userId int,
      @firstName varchar(100),
      @lastName varchar(100),
      @pagerId int,
      @logBy varchar(10)

SELECT @userId = id, @firstName = firstName, @lastName = lastName, @pagerId = pagerId, @logBy = logBy FROM Inserted

-- Insert data
INSERT INTO Pagers_Users_Log
(dbAction, userId, newFirstName, newLastName, newPagerId, dateTime, logBy)
VALUES
('Insert', @userId, @firstName, @lastName, @pagerId, GETDATE(), @logBy)
*************************************

but that gave me the error:

    "Error 311: Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."


Does this mean our whole thought process for doing this needs to be re-worked?

Basically, we need to insert some data into a table (including text types), then have the trigger copy over that data to the log table, but it doesn't want to play nice...

Any ideas?
LVL 6
pillbug22Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
basically you may have problems with TEXT / IMAGE COLUMNS

you may be able to use INSTEAD OF TRIGGERS to achieve your requirements.... instead of the
standard after triggers....
you can reference the text(etc) columns in an after trigger and so get the changed picture...
but this would mean you where in effect pre-storing the column before changes where made and
 then later updateing a changed on date....  
 

from BOL
WRITETEXT  will not cause a trigger to fire



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.
If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PaulBarbinCommented:
Why are you using a text field to hold a firstname and lastname?  Seems like the answer is to change the data types on the original table then go with Plan A.

Paul
PaulBarbinCommented:
FYI, you could also look into Instead Of Triggers if you're using SQL 2K.  They apparently CAN handle text, ntext, and image columns.

Paul
pillbug22Author Commented:
The INSTEAD OF "trick" worked - thanks for the kick in the right direction!
jjradhaCommented:
See the code bellow for an example of how I deal with this.
The example just creates some triggers that insert into a HISTORY table for auditing purposes.
Regards,
Marcello Miorelli
if exists (select * 
             from dbo.sysobjects 
            where id = object_id(N'TestResults_History') 
              and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table TestResults_History
GO
 
CREATE TABLE TestResults_History (
 
 
	TestID int NOT NULL ,
	JobNo nchar (7)  NOT NULL ,
	Dt smalldatetime NOT NULL default (getdate()),
	ItemID nchar(30) NOT NULL ,
	TestNo smallint  default 0 ,
	Serial_from nchar (10)  null default NULL ,
	Serial_to nchar (10)  null default NULL ,
        Inspector nvarchar(50) not null default (USER_NAME()),
	Qty smallint NOT NULL default 0,
	Qty_Pass smallint NOT NULL default 0,
	Qty_Fail smallint NOT NULL default 0,
	Qty_Accepted smallint NOT NULL default 0,
	Comments text  NULL default null,
 
	dt_ datetime NOT NULL default (getdate()),
	user_ nvarchar (50)  NOT NULL default (user_name()),
	AlterationID smallint NOT NULL default 1
 
)
go
 
 
alter table TestResults_History
add constraint PKTestResults_History
primary key (ItemID, dt_,user_)
go
 
if exists (select * 
           from dbo.sysobjects 
           where id = object_id(N'TestResults_for_insert') 
           and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger TestResults_for_insert
GO
create TRIGGER TestResults_for_insert
ON TestResults
for insert
AS
BEGIN
/*-----------------------------------------------------*\
  just to keep track of exact time and user
  Marcelo Miorelli 19/March/2008
\*-----------------------------------------------------*/
 
declare @Message varchar(1008);
begin transaction T1;
 
insert INTO TestResults_History
	SELECT i.TestID, 
	       i.JobNo, 
	       i.Dt, 
	       i.ItemID, 
	       i.TestNo, 
	       i.Serial_from, 
	       i.Serial_to, 
	       i.Inspector, 
	       i.Qty, 
	       i.Qty_Pass, 
	       i.Qty_Fail, 
	       i.Qty_Accepted, 
	       T.Comments,
 
               getdate(),
               user_name(),
               2
	   from inserted i
           join TestResults T on i.TestId = T.TestId
 
    if (@@error <> 0) begin
 
        rollback transaction T1;
 
	    select @Message = 'Inside trigger TestResults_History ' + 
	                      'Contact Marcelo Miorelli at 0 1524 264 105';
	
	    EXEC master.dbo.xp_logevent 60000, @Message, Error;
	    raiserror(@Message,16,1);
 
     end /*if*/
     else begin
 
           commit transaction T1
 
     end /*else*/
 
END
go
 
 
if exists (select * 
           from dbo.sysobjects 
           where id = object_id(N'TestResults_for_update') 
           and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger TestResults_for_update
GO
create TRIGGER TestResults_for_update
ON TestResults
for update
AS
BEGIN
/*-----------------------------------------------------*\
  just to keep track of exact time and user
  Marcelo Miorelli 19/March/2008
\*-----------------------------------------------------*/
 
declare @Message varchar(1008);
begin transaction T1;
 
insert INTO TestResults_History
	SELECT i.TestID, 
	       i.JobNo, 
	       i.Dt, 
	       i.ItemID, 
	       i.TestNo, 
	       i.Serial_from, 
	       i.Serial_to, 
	       i.Inspector, 
	       i.Qty, 
	       i.Qty_Pass, 
	       i.Qty_Fail, 
	       i.Qty_Accepted, 
	       T.Comments,
 
               getdate(),
               user_name(),
               1
	   from inserted i
           join TestResults T on i.TestId = T.TestId
 
 
    if (@@error <> 0) begin
 
        rollback transaction T1;
 
	    select @Message = 'Inside trigger TestResults_History ' + 
	                      'Contact Marcelo Miorelli at 0 1524 264 105';
	
	    EXEC master.dbo.xp_logevent 60000, @Message, Error;
	    raiserror(@Message,16,1);
 
     end /*if*/
     else begin
 
           commit transaction T1
 
     end /*else*/
 
END
go
 
if exists (select * 
           from dbo.sysobjects 
           where id = object_id(N'TestResults_for_delete') 
           and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger TestResults_for_delete
GO
create TRIGGER TestResults_for_delete
ON TestResults
for delete
AS
BEGIN
/*-----------------------------------------------------*\
  just to keep track of exact time and user
  Marcelo Miorelli 19/March/2008
\*-----------------------------------------------------*/
 
declare @Message varchar(1008);
begin transaction T1;
 
insert INTO TestResults_History
	SELECT i.TestID, 
	       i.JobNo, 
	       i.Dt, 
	       i.ItemID, 
	       i.TestNo, 
	       i.Serial_from, 
	       i.Serial_to, 
	       i.Inspector, 
	       i.Qty, 
	       i.Qty_Pass, 
	       i.Qty_Fail, 
	       i.Qty_Accepted, 
	       T.Comments,
 
               getdate(),
               user_name(),
               2
	   from deleted i
           join TestResults T on i.TestId = T.TestId
 
 
    if (@@error <> 0) begin
 
        rollback transaction T1;
 
	    select @Message = 'Inside trigger TestResults_History ' + 
	                      'Contact Marcelo Miorelli at 0 1524 264 105';
	
	    EXEC master.dbo.xp_logevent 60000, @Message, Error;
	    raiserror(@Message,16,1);
 
     end /*if*/
     else begin
 
           commit transaction T1
 
     end /*else*/
 
END
go

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.