May 10, 2010 at 3:19 pm
I am getting the following error and hoping I can get some assitance on this.
Msg 311, Level 16, State 1, Procedure cusPopulateGECounty, Line 7
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
The mDataValue field comes off my cuscustomcontrolpatientdata table and is a text object.
Code Below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER cusPopulateGECounty ON dbo.cuscustomcontrolpatientdata
FOR INSERT , UPDATE
AS
BEGIN
SET NOCOUNT ON ;
UPDATE
pp
SET
pp.county = CAST(c.mDataValue as VARCHAR(8000))
FROM
inserted c
JOIN cuscustomcontroldetail d ON c.customcontroldetailid = d.customcontroldetailid
JOIN patientprofile pp ON c.patientprofileid = pp.patientprofileid
END
GO
May 11, 2010 at 1:16 am
Hello,
I'm afraid there isn't much we can help with. The column you reference is TEXT datatype, which is not allowed. Trying to convert the value doesn't help, it just isn't accessible in the inserted and deleted tables.
If you can, change the datatype of this column from TEXT to VARCHAR(max) - however, you have to be careful, since there could be some code relying on this column as TEXT. If you can't change datatypes, you will not be able to reference that column in a trigger and have to find some other way to solve your problem.
May 11, 2010 at 4:33 am
yaah...! thts right if you have a text or a image datatype column in the table and you try to trigger any action from trigger then you get that error
May 11, 2010 at 9:44 am
If you are really need to implement this logic in a trigger, use INSTEAD OF one. With appropriate compatibility level (80 or higher) - it should work.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply