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