Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: text (or ntext) concatenation

    i want to concatenate 2 columns of type text

    what am trying to do is adding an instead of trigger on update which concatenates the old value in the text column with the new value

    so the problem is how i can concatenate both values since i cant declare a variable of type text.

    A solution may be dividing the text column into many varchar(8000) variables and then add them but i dont know how it can be implemented or if there's an easier solution

    the code am trying:

    CREATE TRIGGER InsteadTrigger on tbl
    INSTEAD OF Update
    AS
    BEGIN

    if exists(select null from deleted)
    begin
    --am considering updating only 1 row
    update tbl set theValue=((select theValue from deleted)+(select theValue from inserted))
    end



    i tried the writetext and updatetext functions and the problem is always related to both columns concatenation


    Any solution?

    thx
    samham

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    I am assuming that this trigger is for an update y

    something to the effect of
    create trigger
    ----------
    ----
    as
    update X
    set x.col2 = D.col2 + '\' + I.col2
    from table1 as x join inserted as I
    on x.col1 = I.col1
    join deleted as D
    on d.col1 = i.col1

    this will look like "oldvalue\newvalue"
    Last edited by Ruprect; 04-04-04 at 14:32.

  3. #3
    Join Date
    Jan 2003
    Posts
    15
    I tried it and got the following error:

    Cannot use text, ntext or image columns in the "inserted" and "deleted" tables


    heres the full code:

    CREATE TRIGGER trig ON [dbo].[tbl2]
    FOR UPDATE
    AS
    update tbl2
    set tbl2.theValue = D.theValue + '\' + I.theValue
    from tbl2 as x join inserted as I
    on x.theID = I.theID
    join deleted as D
    on d.theID = i.theID



    my table tbl2 contains 2 columns theID of type int and theValue of type text


    the concatenation of 2 columns of type text (using the '+' operator) is not accepted by sql server

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i dont know a workaround i will look into it tomorrow

    anyone else


    anyone

    buehler?
    buehler?

    buehler?

Posting Permissions

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