Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002

    Unanswered: how to change colum values in a trigger


    Does anyone of you know how I can create a trigger to do the following

    Table tbs contain 3 columns : total, num_col and alpha_col

    I'm doing an insert in table tbs:
    insert into tbs (total) values ('111aaa')

    Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.

    I'm working on a MS SQL Server 7

    Can anyone help ?!?!?

  2. #2
    Join Date
    Jan 2002
    I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.

    DECLARE @vInput VARCHAR(20);
    DECLARE @vNum VARCHAR(10);
    DECLARE @vAlpha VARCHAR(10);
    DECLARE @vPosition INTEGER;

    SET @vInput = '111aaa';
    SET @vNum = '';
    SET @vAlpha = '';

    SET @vPosition = 1;
    WHILE @vPosition <= DATALENGTH(@vInput)
    WHILE ASCII(SUBSTRING(@vInput, @vPosition, 1)) BETWEEN 48 AND 57
    SET @vNum = @vNum + SUBSTRING(@vInput, @vPosition, 1);
    SET @vPosition = @vPosition + 1;
    END -- while number
    SET @vAlpha = @vAlpha + SUBSTRING(@vInput, @vPosition,1);
    SET @vPosition = @vPosition + 1;
    END -- while string

    print @vNum;
    print @vAlpha;

Posting Permissions

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