Making auto generated numbers in SQL server using triggers

I searched long and hard for this solution on the net but to no avail but got hints on how to do it on a few sites but the problem was I was that type of programmer who avoided using the database for anything but storage. I preferred my database as a reservoir of data no thinking/intelligence apart from the auto generated integers offered by the DBMS until a client asked for a prefixed six digit auto generated sequential code to identify each record in the system. This became my agony for weeks, not that I was clueless but I had ideas (quick escape) a solution for that moment as I think of a more permanent one. To cut the long story short all those ideas failed but the one which worked is this one (using a trigger). I know many of you will say y triggers not just a function to check the last number(CHKLST) in the database and then just add 1 to that number, this is y, in a distributed environment there is a chance that the function(CHKLST) which checks the last value/number in the database gets indeed the value and increase it by 1 and hands over to the function that writes (WRITER) to the database but before the function(WRITER) writes to the database a delay occurs say network congestion but the (CHKLST) manages to get the last value again before function(WRITER) writes the new value. This means that a duplicate of values will exist which is no good. I hope you’ve all gotten my point but the way I resolved the problem is this any improvements are welcome; humans are learning beings we can adapt to change.

ALTER TRIGGER [dbo].[triggerName]

ON [dbo].[tableName]

AFTER INSERT

AS

BEGIN

DECLARE @DBautogeneratedvalue int;

DECLARE @code char(10);

SELECT @DBautogeneratedvalue =autoid FROM inserted;

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

if(@DBautogeneratedvalue BETWEEN 1 AND 9)

SET @code ='X00000'+CONVERT(char(10),@DBautogeneratedvalue)

ELSE IF(@DBautogeneratedvalue BETWEEN 10 AND 99)

SET @code ='X0000'+CONVERT(char(10),@DBautogeneratedvalue)

ELSE IF (@DBautogeneratedvalue BETWEEN 100 AND 999)

SET @code ='X000'+CONVERT(char(10),@DBautogeneratedvalue)

ELSE IF (@DBautogeneratedvalue BETWEEN 1000 AND 9999)

SET @code ='X00'+CONVERT(char(10),@DBautogeneratedvalue)

ELSE IF (@DBautogeneratedvalue BETWEEN 10000 AND 99999)

SET @code ='X0'+CONVERT(char(10),@DBautogeneratedvalue)

ELSE

SET @code ='X'+CONVERT(char(10),@DBautogeneratedvalue)

SET NOCOUNT ON;

UPDATE tableName SET Code=@code WHERE autoid=@DBautogeneratedvalue;

-- Insert statements for trigger here

END

Where Code is the field of the required new code and autoid is the field with the database auto generated integer number.