Example of trigger for auto increment ID

ALTER TRIGGER [dbo].[CEBC_TRIGGER_UPDATE_INVOICE_NUMBER]
   ON [dbo].[Invoice]
AFTER INSERT
 
AS
BEGIN
DECLARE @INVOICE_NUMBER VARCHAR(MAX),@TEMP_NUMBER INT
SELECT @TEMP_NUMBER=MAX(SUBSTRING(invoiceNo,4, LEN(invoiceNo) -3)) FROM INVOICE
IF LEN(@TEMP_NUMBER)>0
BEGIN
SET @TEMP_NUMBER=@TEMP_NUMBER+1
SET @INVOICE_NUMBER='INV'+REPLICATE('0',(6-LEN(@TEMP_NUMBER)))+convert(varchar,@TEMP_NUMBER)
BEGIN TRY
BEGIN TRANSACTION
               BEGIN
UPDATE INVOICE
SET INVOICE.invoiceNo=@INVOICE_NUMBER
FROM [dbo].[Invoice]
INNER JOIN INSERTED  ON (INVOICE.Id = INSERTED.Id)
END
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
               BEGIN
UPDATE INVOICE
SET INVOICE.invoiceNo='INV000001'
FROM [dbo].[Invoice]
INNER JOIN INSERTED  ON (INVOICE.Id = INSERTED.Id)
END
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END CATCH
END


END