Solved

Use 'text' data types in triggers?

Posted on 2003-12-03
5
3,785 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now