getting same value in a table after triggering action

See more:

SQL-Server

hi, i need to generate one id column which increments automatically and i want concatenate another column with this column

i written the following trigger but it always returning the same value for every insertion in the table

ex.Document_Id = MGA0001 for every insertion

ALTER TRIGGER Document_Management_Trigger ON Document_Management AFTER INSERT
AS
BEGIN
DECLARE @ID INT = 0
DECLARE @Document_Type INT = 0
DECLARE @Doc_Type NVarchar(2) = NULL
DECLARE @Autogenerate_Id NVarchar(10) = NULL
DECLARE @New_Autogenerate_Id NVarchar(10) = NULL
DECLARE @Character CHAR(1) = NULL
DECLARE @Number INT = 0


SELECT @ID = I.ID FROM INSERTED I;

SELECT @Document_Type = I.Document_Type FROM INSERTED I;
SELECT @Doc_Type = I.Doc_Type FROM INSERTED I;
DECLARE @Document_Mana INT = 0

SET @Autogenerate_Id = (SELECT TOP 1 Autogenerate_ID FROM Document_Management DM WHERE ID = (SELECT MAX(ID) FROM Document_Management WHERE Document_Type = @Document_Type))

IF @Autogenerate_Id IS NULL
BEGIN
    SET @Autogenerate_Id = 'A0000'
END

SET @Character = (SELECT SUBSTRING (@Autogenerate_Id ,1, 1))

SET @Number = (SELECT SUBSTRING (@Autogenerate_Id ,2, 4))

SET @Number = @Number + 1

IF @Number > 9999
BEGIN
   SET @Number = 1

   SET @Character = CHAR(ASCII(@Character)+1)
END

SET @Autogenerate_Id = @Character + RIGHT(CONVERT(VARCHAR(4),@Number),4)


UPDATE Document_Management SET Autogenerate_Id = @Autogenerate_Id, Document_Id = @Doc_Type + @Autogenerate_Id WHERE ID = @ID

END