how to change colum values in a trigger

Hi

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 ?!?!?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;
DECLARE @.vNumPos INTEGER;

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

SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
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;sql