Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: using 'text' type column in delete trigger from deleted table

    Hi
    I have a delete trigger which needs to insert data (for archiive purpose) from deleted table . here is the code :
    create trigger td_member_message on member_message
    after delete
    as

    begin
    declare @numrows int,
    @errno int,
    @errmsg varchar(255),
    @now datetime

    select @numrows = @@rowcount, @now = getdate()

    if @numrows = 0
    return

    insert member_message_arch
    select d.*, @now
    from deleted d

    return

    /* Errors handling */
    error:
    raiserror @errno @errmsg
    rollback transaction
    end
    go

    But I am getting error here like:
    Msg 311, Level 16, State 1, Procedure td_member_message, Line 20
    Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

    I know using of instead of trigger can get rid of this error but I cannot think of using instead of trigger in this case.

    CAn anyone pls help me?
    Thanks in advance
    Reply With Quote

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give the DDLs of the tables member_message and member_message_arch.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    CREATE TABLE [dbo].[MEMBER_MESSAGE] (
    [message_id] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
    [type] [tinyint] NOT NULL ,
    [subject] [varchar] (255) NULL ,
    [body] [text] NULL ,
    [ref] [numeric](10, 0) NULL ,
    [created] [datetime2] NULL ,
    [modified] [datetime2] NULL ,

    constraint pk_member_message primary key clustered (member_message_id) )

    -----------------------------

    Member message archive has the same structure with extra field dattime

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL Server version please?

    text was deprecated in SQL Server 2005 and the advice is to use varchar(max) or nvarchar(max) in its place
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2009
    Posts
    6
    sqlserver veriosn 2008
    thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change your datatype to varchar(max) and your problem should go away
    George
    Home | Blog

Posting Permissions

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