Solved

Problem with text datatype in trigger creation..

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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