Solved

Problem with text datatype in trigger creation..

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now