Results 1 to 10 of 10

Thread: Text data type from deleted table (trigger)

  1. #1
    Join Date
    Feb 2003
    Posts
    13

    Text data type from deleted table (trigger)

    Hi,

    I need to prevent modification/update to a field.

    So I created a trigger. To take the data from DELETED table then replace the field data.

    However, I have problem with one field which data type is text.

    MS SQL always return me this error:
    Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

    I use the following code to take the data from deleted table

    DECLARE @ptrval varbinary(16)
    SELECT @ptrval = TEXTPTR(NOTES )
    FROM DELETED

    I also tried simple Select statement
    Select notes from deleted

    What code should I use to take the deleted data

    Please help.

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70.

    If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.


  3. #3
    Join Date
    Feb 2003
    Posts
    13
    Hi Rmiao,

    I have set the compatibility to 80

    however, I'm still not able to use the instead of trigger as u suggested. Is there anything wrong the syntax.

    The purpose of my trigger is to prevent user from updating Notes field in Contsupp database

    Below is my trigger


    CREATE TRIGGER NO_UPDATE_TO_NOTES
    ON CONTSUPP
    INSTEAD OF UPDATE
    AS

    IF UPDATE (NOTES)
    BEGIN
    DECLARE @@CONTACT CHAR(30)
    SELECT @@CONTACT=LTRIM(RTRIM((SELECT U_CONTACT FROM DELETED)))
    IF @@CONTACT='COMMENTS'
    BEGIN
    UPDATE CONTSUPP SET NOTES=(SELECT NOTES FROM DELETED)
    WHERE RECID=(SELECT RECID FROM DELETED)
    END
    END
    ==================
    Server: Msg 279, Level 16, State 3, Procedure NO_UPDATE_TO_NOTES, Line 12
    The text, ntext, and image data types are invalid in this subquery or aggregate expression.


    Thanks in advance

    rgds,

    Christine

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Is notes a text column? If so, can't use it in subquery.

  5. #5
    Join Date
    Feb 2003
    Posts
    13
    Yes, Notes field is text column.

    mmm, any suggestion how to prevent them from editing that particular column?

  6. #6
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    Try to refine your Update as follows:

    UPDATE contsupp
    SET notes = d.notes
    FROM contsupp cs
    join deleted d on d.recid = cs.recid

  7. #7
    Join Date
    Feb 2003
    Posts
    13

    Unhappy

    Still can't, andi. Hikss..

  8. #8
    Join Date
    Nov 2002
    Location
    Madison, WI
    Posts
    4
    I haven't tested this today, but in times past I have sometimes pulled data from a text field by concatenating parts of the field.

    SQL-Server lets you do things with a SUBSTRING that can't be done by selecting the text data type column directly.

    SELECT SUBSTRING(textfield,1,2000)
    || SUBSTRING(textfield,2000,2000)
    || SUBSTRING(textfield,4000,2000)
    || SUBSTRING(textfield,6000,2000)
    from tablename

    Maybe this will give an alternate solution that could work around the problem.

  9. #9
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    Hi Christine

    I have checked this out on my development machine (Win2k, SQL2K SP3) and it works.

    * That's the table definition I am using:
    CREATE TABLE [dbo].[tblTest] (
    [pk_test] [int] IDENTITY (1, 1) NOT NULL ,
    [txt_test] [text] COLLATE SQL_Latin1_General_CP437_CI_AS NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tblTest] ADD
    CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
    (
    [pk_test]
    ) ON [PRIMARY]
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE TRIGGER NO_UPDATE_TO_NOTES
    ON tblTest
    INSTEAD OF UPDATE
    AS

    IF UPDATE (txt_test)
    BEGIN
    UPDATE tbltest
    SET txt_test = d.txt_test
    FROM tbltest t
    join deleted d on d.pk_test = t.pk_test
    END
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    * My test data:
    pk_test,txt_test
    1,Rec 1
    2,Rec 2
    3,Rec 3

    * My Update statement:
    Update tbltest set txt_test = 'Rec 1 modified' where pk_test = 1

    * The data after the update:
    pk_test,txt_test
    1,Rec 1
    2,Rec 2
    3,Rec 3

  10. #10
    Join Date
    Feb 2003
    Posts
    13

    Talking

    Hi Andi, thanx
    It works well.

    Thanx to David and rmiao too.
    Last edited by Christine; 03-13-2003 at 09:18 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •