?
Solved

Problem with text datatype in trigger creation..

Posted on 2006-11-09
3
Medium Priority
?
319 Views
Last Modified: 2008-02-01
Hi,

I am getting below error while creating trigger mentioned below: Please help me out.. I need to use the information for deleted / inserted table. I know i cannot change the datatypes from text to varchar/char....

Message : (37000)(311) [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
MSDUV260 - READSQL : Cannot execute SQL


CREATE TRIGGER DBO.TU_Remark
ON DBO.XXX_Remark
AFTER UPDATE
AS
      DECLARE @componentId INT;
      DECLARE @auditHistoryId INT;
      DECLARE @statusObj INT;
      DECLARE @statusComp INT;
      DECLARE @componentType INT;
      DECLARE @crc INT;

--      EXEC DBO.SearchAuditTransaction @auditHistoryId, @componentType, @statusObj;
      EXEC DBO.SearchAuditTransaction @auditHistoryId, @componentType, @statusObj;

      IF @auditHistoryId <> 0
      BEGIN
            SET @statusComp = 0;
            INSERT INTO DBO.AUD_Remark SELECT d.RemarkID, d.RemarkText, d.RowPub, d.RowState FROM DELETED d;
            SELECT @componentId = @@identity;
            SET @crc = DBO.GenerateControlValue('AUD_Remark', @componentId);
            INSERT INTO DBO.XRefComponent VALUES(@auditHistoryId, @componentId, 5,@statusComp, @crc);

            IF EXISTS(SELECT * FROM DBO.DT_PrevSchedule DT WHERE DT.ComponentId = (SELECT DISTINCT ps.PrevScheduleId FROM DBO.Entretien e, DBO.PrevSchedule ps, DBO.History h,DELETED d WHERE e.TypeCause = 3 AND e.CauseLink = d.RemarkID AND e.ActivityLink = ps.ActivityLink AND e.AssetLink = ps.AssetLink AND e.ActivityOffset = ps.ActivityOffset) AND DT.AuditHistoryId <> @auditHistoryId)
            BEGIN
                UPDATE DBO.DT_PrevSchedule SET Version = Version + 1, AuditHistoryId = @auditHistoryId
FROM DBO.DT_PrevSchedule DT
WHERE DT.ComponentId = (SELECT DISTINCT ps.PrevScheduleId FROM DBO.Entretien e, DBO.PrevSchedule ps, DBO.History h,DELETED d WHERE e.TypeCause = 3 AND e.CauseLink = d.RemarkID AND e.ActivityLink = ps.ActivityLink AND e.AssetLink = ps.AssetLink AND e.ActivityOffset = ps.ActivityOffset) AND DT.AuditHistoryId <> @auditHistoryId;
            END
            IF EXISTS(SELECT * FROM DBO.DT_PrevSchedule DT WHERE DT.ComponentId = (SELECT DISTINCT ps.PrevScheduleId FROM DBO.PrevSchedule ps, DBO.History h,DELETED d WHERE ps.PrevScheduleId = h.PrevScheduleLink AND h.RemarkLink = d.RemarkID) AND DT.AuditHistoryId <> @auditHistoryId)
            BEGIN
                  UPDATE DBO.DT_PrevSchedule SET Version = Version + 1, AuditHistoryId = @auditHistoryId
                  FROM DBO.DT_PrevSchedule DT
                  WHERE DT.ComponentId = (SELECT DISTINCT ps.PrevScheduleId FROM DBO.PrevSchedule ps, DBO.History h,DELETED d WHERE ps.PrevScheduleId =                   h.PrevScheduleLink AND h.RemarkLink = d.RemarkID) AND DT.AuditHistoryId <> @auditHistoryId;
            END
      END



0
Comment
Question by:homnath_sharma
[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
3 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 17919599
The message is quite clear you are SOL with that approach.  You are going to have to find a workaround.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

764 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