Solved

Problem with text datatype in trigger creation..

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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…

773 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