You can do it this way suppose the table name is mytable
create table mytable(
id varchar(6) primary key,
name varchar(30),
someint int identity(0,1)
);
for this create a trigger which looks something like this
create trigger [dbo].[dummy]
on [dbo].[mytable]
for insert
as
declare @id varchar(6);
declare @someint int;
select @id=id from inserted;
select @someint=someint from inserted;
if(@someint<10)
begin
update mytable
set id='M'+'00'+convert(varchar,@someint) where id=@id
end
else
if(@someint>=10 and @someint<100)
begin
update mytable
set id='M'+'0'+convert(varchar,@someint) where id=@id
end
else
if(@someint>=100 and @someint<1000)
begin
update mytable
set id='M'+convert(varchar,@someint) where id=@id
end
else
rollback
I think this must solve your question