pillbug22
asked on
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]
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]
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?
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]
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]
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Paul
ASKER
The INSTEAD OF "trick" worked - thanks for the kick in the right direction!
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
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
Paul