How do I read inserted text data in a trigger?
Hello,
I am using SQL Server 2000 with Service Pack 3 installed.
I have a trigger. The source table for the trigger contains a text column.
I am trying to capture that text variable value into a local variable. Every combination I try I always get the error: Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
The reason I am trying to do this, is that later in the trigger code, I want to insert this text data into a surrogate real table.
Below are my attempts.
DROP TRIGGER TRIns_client_contract_comments
GO
CREATE TRIGGER TRIns_client_contract_comments
ON client_contract_comments
FOR INSERT AS
BEGIN
declare @vclient_id numeric
declare @vclient_contract_num varchar
declare @vclient_service_type_cd char
declare @vcomment_type char
declare @vcomments varchar(8000)
-- TEXT INVALID FOR LOCAL VARIABLES declare @vtcomments text
declare @vlast_update_userid char
declare @vlast_update_dt datetime
/*
I ALWAYS GET THIS MESSAGE
Cannot use text, ntext, or image columns
in the 'inserted' and 'deleted' tables.
*/
declare @ptrval binary(16)
-- FAIL 'CANNOT USE' select @ptrval=textptr(comments) from inserted
-- FAIL 'CANNOT USE' select comments into #comments from inserted
-- declare @substr varchar(10)
-- FAIL CAN NOT USE' select @substr = upper(substring(comments,1,5)) from inserted
select @vclient_id = ins.client_id,
@vclient_contract_num = ins.client_contract_num,
@vclient_service_type_cd = ins.client_service_type_cd,
@vcomment_type = ins.comment_type,
-- FAIL 'CANNOT USE' @vcomments = ins.comments,
@vlast_update_userid = ins.last_update_userid,
@vlast_update_dt = ins.last_update_dt
from inserted ins
END
GO
Any ideas or workarounds?
Thanks.
AIMDBA
[1943 byte] By [
AIMDBA] at [2007-12-19]
This is documented (as well as the solution) in the BOL under CREATE Trigger:
"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 set 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 you got any problems using the suggestions mentioned in the BOL, just raise a hand.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Early, I said that I am using SQL Server 2000 with Service Pack 3. This is compatiblity 80 (not 65 and not 70).
The BOL solution for 80 is the following:
"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."
I can not write an INSTEAD OF trigger. This would break what I am trying to do.
Microsoft has a bug report on this 'inability of reading text data inside of a trigger.' The only possibly solution mentioned is to apply a hotfix.
In my environment, this would be applied to a production machine. I can not do this immediately. This means not this year, in 2006.
Do any more ideas or workarounds exist?
Thanks.
AIMDBA
Why not an INSTEAD OF trigger? I can't think of anything you couldn't do in the instead of trigger (though it will bust identity usage functions, so you would have to change your code to just use the table's alternate key for access to get the last inserted row.)
Note also that your trigger would not work correctly right now for multiple rows. The variables would simply get the last row in the set.
Short of this, if you are not doing data valiation, consider just putting the key of the table in a queue and have an external process work on the rows asynchronously.
As for a bug report, I doubt there a hotfix because 2005 doesn't allow it either. It does allow varchar(max) (the replacement for text columns) in triggers.
Get the text data from the table itself, joining to inserted on the primary key, so you're only looking at inserted records. Remember that when the trigger fires, the insert has already occurred. If the trigger does a ROLLBACK, of course, it will be deleted.
You still can't declare a local text variable (until SQL 2005, where you can use varchar(max)), but you can at least inspect the data using substring(), or other functions that operate on text data. (I haven't actually tried this, but I don't see why it wouldn't work...)
Ken
