Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

trigger with ntext value

Posted on 2009-05-11
4
Medium Priority
?
499 Views
Last Modified: 2012-05-06
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
Comment
Question by:MimUK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24354557
you cannot update the text column with an update statement, you need to use UPDATETEXT /WRITETEXT
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24354573
MimUK said:
>>Please can anyone suggest a workaround?

If you are using SQL Server 2005 or later, use nvarchar(max) instead of ntext.
0
 

Author Comment

by:MimUK
ID: 24354577
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
 
LVL 22

Accepted Solution

by:
dportas earned 375 total points
ID: 24357521
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question