SQLTeam.com | Weblogs | Forums

What variable type could I use in a trigger to read information from a text field


#1

What variable type could I use in a trigger to read information from a text field.

I tried using nvarchar(MAX)

declare @Comments nvarchar(MAX)

But I get error messages:

Msg 311, Level 16, State 1, Procedure UpdateAssetHistory, Line 82
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Msg 311, Level 16, State 1, Procedure UpdateAssetHistory, Line 172
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

on this statement:
select @Comments=d.comments from deleted d

and this statement
select NEXT VALUE FOR AssetHistory_Seq,GETDATE(),CreatedBy,CreatedByID,GETDATE(),LastModBy,LastModByID,AssetStatus,AssetTag,AssetUser,AssetUserID,EmployeeNumber,CellPagerNo,CountyTag,DisposalType,DistrictSection,eCapsUnit,FacilityCode,FacilityName,
Model,ModelNumber,PhoneNUmber,SerialNumber,TicketShipNumber,DataSource,RecID,BudgetName,CG4UpdatedBy,CG4UserName,'UPDATED',LastUpdatedBy,LastUpdatedDateTime,LastUpdatedEmpid, LocationFloor,CubicleRoom,LotPallet,
EquipmentType,ModelDescription,Ownership,PONumber,Brand,Category,PurchasePrice,IMEI,MEID,ICCID,Manufacturer,PA2197,comments from deleted

I appreciate your help.


#2

I would have run your code and tried a few possible solution if the complete code had been provided.

Try replacing

by

If it doesn't work, can you provide the script for the trigger? I'll try a few alternatives on my machine. No need to include all the columns, just an id and the offending column.

With kind regards
Wim