• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

trigger with ntext value

I have a trigger,
But am having a problem updating the comp_notmaintained value as its of type ntext.
If I remove this value it works with the other two.

Please can anyone suggest a workaround?

Many thanks,

Mim
CREATE trigger [trg_updatecomp]
  on [dbo].[Installed]
 for update
as
  update Company
    set comp_inststatus
      = i.inst_status, comp_expirydate = i.inst_expirydate, comp_notmaintained = i.inst_notmaintained
from Installed q 
join inserted i
on i.inst_companyid = q.inst_companyid

Open in new window

0
MimUK
Asked:
MimUK
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
you cannot update the text column with an update statement, you need to use UPDATETEXT /WRITETEXT
0
 
Patrick MatthewsCommented:
MimUK said:
>>Please can anyone suggest a workaround?

If you are using SQL Server 2005 or later, use nvarchar(max) instead of ntext.
0
 
MimUKAuthor Commented:
Please could you help with what I need to replace in the trigger.
i cannot change the column type as its fixed in the application

Mim
0
 
dportasCommented:
This should work:

CREATE TRIGGER [trg_updatecomp]
  ON [dbo].[Installed]
 FOR UPDATE
AS
  UPDATE Company
    SET comp_inststatus
      = i.inst_status, comp_expirydate = i.inst_expirydate, comp_notmaintained = q.inst_notmaintained
  FROM Installed q
  JOIN inserted i
  ON i.inst_companyid = q.inst_companyid
  JOIN Company
  ON i.inst_companyid = Company.inst_companyid ;
GO

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now