Solved

Problem with text datatype in trigger creation..

Posted on 2006-11-09
3
316 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 50 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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