March 27, 2003 at 9:11 am
I am trying to run a trigger which involes
the following cmd:
'select @TableName = 'trigtest'
select * into #ins from inserted
select * into #del from deleted'
I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Can someone suggest how to deal with text fields? I think the reason is that the table has a text field. when I specify the fields in the select statement and do not include the text field then it works fine.
March 27, 2003 at 9:34 am
If you used the select of a text field in the trigger, then the lenght of the text values should be not too big.
Execute SELECT @@TEXTSIZE to know the lenght you have. And also increase it with SET TEXTSIZE newnumber (for example : 64512)
and try again.
March 27, 2003 at 9:50 am
Nope...even after reducing the text size...it still gives me the same error!
March 27, 2003 at 10:07 am
SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER.
To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table.
March 27, 2003 at 10:15 am
I would try increasing it, not reducing it.
March 27, 2003 at 10:21 am
Thanks so much Allen.
It works perfectly now!!!!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply