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.
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, DELETENOT FOR REPLICATIONASDECLARE @TrigTime DateTimeset @TrigTime = getDate()UPDATE ocdb_History_ProductSET ocdb_History_enddatetime = (@TrigTime), ocdb_History_endappname = (APP_Name()), ocdb_History_endusername = (USER_Name()), ocdb_History_endhostname = (HOST_NAME()) FROM deleted,ocdb_History_ProductWHERE ocdb_History_Product.Product_Num = deleted.Product_NumAND 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()FROMinserted-----------------------End Trigger---------------------Product_Num is the PK and its type is VaracharWhat should be the cause for the error?ThanksYossi |
|
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 BOLIn 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.HTHJasper Smith |
|
|
yossibaram
Yak Posting Veteran
82 Posts |
Posted - 2002-08-25 : 12:18:44
|
Thanks mateYossiquote: 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 BOLIn 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.HTHJasper Smith[/quote] |
|
|
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?ThanksSwati |
|
|
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 suggestThanksSwati |
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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 !ThanksSwati |
|
|
|
|
|
|
|