Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error 311/170 in a trigger

Author  Topic 

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-25 : 03:25:20
hi,
I get this error when in the following trigger:
"Cannot use text, ntext or image columns in the inserted and deleted tables"
--------Trigger----------------------------------------------
CREATE TRIGGER "ocdb_history_trigger_Product" ON "Product"
FOR INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
DECLARE
@TrigTime DateTime
set @TrigTime = getDate()

UPDATE
ocdb_History_Product
SET
ocdb_History_enddatetime = (@TrigTime),
ocdb_History_endappname = (APP_Name()),
ocdb_History_endusername = (USER_Name()),
ocdb_History_endhostname = (HOST_NAME())
FROM
deleted,ocdb_History_Product
WHERE
ocdb_History_Product.Product_Num = deleted.Product_Num
AND
ocdb_History_enddatetime = '9/9/9999'

insert into ocdb_History_Product (
Product_Num,
Product_Name,
Last_Update,
SpecialSigns_Scr1, SpecialSigns_Scr2, SpecialSigns_Scr3, SpecialSigns_Scr4, SpecialSigns_Scr5, SpecialSigns_Scr6, SpecialSigns_Scr7,
SE_Mapping,
Trans_Screen,
Station_ID, SE_Bitmap,
BackOfficeStickerType,
Label_Type,
ocdb_history_startdatetime,
ocdb_history_enddatetime,
ocdb_history_startusername,
ocdb_history_startappname,
ocdb_history_starthostname
)
SELECT
Product_Num,
Product_Name,
Last_Update,
SpecialSigns_Scr1, SpecialSigns_Scr2, SpecialSigns_Scr3, SpecialSigns_Scr4, SpecialSigns_Scr5, SpecialSigns_Scr6, SpecialSigns_Scr7,
SE_Mapping,
Trans_Screen,
Station_ID,
SE_Bitmap,
BackOfficeStickerType,
Label_Type,
@TrigTime,
'9/9/9999',
user_name(),
app_name(),
host_name()
FROM
inserted
-----------------------End Trigger---------------------
Product_Num is the PK and its type is Varachar
What should be the cause for the error?
Thanks
Yossi

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-25 : 11:39:36
quote:
Cannot use text, ntext or image columns in the inserted and deleted tables

It's telling you what the problem is. It depends on what version of SQL you are using and your database compatability level as to what your options are. Read up on CREATE TRIGGER in BOL

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.



HTH
Jasper Smith
Go to Top of Page

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-25 : 12:18:44
Thanks mate
Yossi
quote:
Cannot use text, ntext or image columns in the inserted and deleted tables

It's telling you what the problem is. It depends on what version of SQL you are using and your database compatability level as to what your options are. Read up on CREATE TRIGGER in BOL

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.



HTH
Jasper Smith
[/quote]

Go to Top of Page

swatip
Starting Member

8 Posts

Posted - 2009-12-30 : 01:34:00
Hi Jasper,
I am developing one trigger to audit 4 critical tables.In that script I had created the trigger with the AFter clause for update and delete but thrown the same error(Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.)As suggested by you I checked the DB cmpt level which is 80.I created the trigger with the INSTEAD OF due to which trigger on the table got created successfully.But the problem which I am facing now is my table on which trigger has been created not getting updated since I have created the trigger with Instead Of.Is there any workaround to resolve this issue?

Thanks
Swati
Go to Top of Page

swatip
Starting Member

8 Posts

Posted - 2009-12-30 : 02:08:45
Now I had to change the datatype from ntext to nvarchar(max)and executed the trigger.Other than this is ther any workaround?
Please suggest

Thanks
Swati
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 04:32:47
so you actually have sql server 2005 or 2008 then? (since you can use NVARCHAR(MAX)) -- TEXT / NTEXT are depreciated and you should switch to VARCHAR/NVARCHAR(MAX) as they work more like normal strings. -- Are you aware you have posted this in a sql server 2000 forum?

So some good has come from this.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

swatip
Starting Member

8 Posts

Posted - 2010-01-04 : 00:20:33
Hi Charlie,

Thanks for the reply.I will consider nvarchar(max) instead of ntext.
I am using Sql Server 2005 and was facing the issue with the Instead of trigger and posted in this sql 2000 forum since it was relevant to that but anyways thank you for letting me know !

Thanks
Swati
Go to Top of Page
   

- Advertisement -