Solved

Use 'text' data types in triggers?

Posted on 2003-12-03
5
3,791 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:pillbug22
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 9868684
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.

0
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9868707
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
0
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9868732
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
0
 
LVL 6

Author Comment

by:pillbug22
ID: 9869947
The INSTEAD OF "trick" worked - thanks for the kick in the right direction!
0
 

Expert Comment

by:jjradha
ID: 21573197
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

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

680 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