Create customized auto increment Sequence number in SQL Server

Last Reply 18 days ago By bigbear

Posted 25 days ago

Hello all,

How to create Customized Column with Trigger or Function in T-SQL 

I am working on a database column, I'm deciding between Trigger and Function. I believe 1 of these is what I will need because I don't think Stored Procedure can be ran after an insert.

I'm open to any suggestions though, I have a column that will need a string value then the current year of 2 digits followed by a – and a counter after. On the start of a new year I will need the counter to restart at 001.

How should I do this?

EX. VSP20-001, VSP20-002 … and the next year VSP21-001, VSP21-002 …

Here is the start of my TRIGGER. I know it needs a lot more. I'm not sure if I should do it as a computed column or if I can do it all In just 1 column.

CREATE TRIGGER trg_ProposalTest
ON dbo.Serv_Quotes
AFTER INSERT
AS
BEGIN

DECLARE @yearTst nvarchar(10);
SET @yearTst = (SELECT FORMAT(GETDATE(), 'yy'))

DECLARE @word nvarchar(5);
SET @word = 'VSP';

DECLARE @counter int;
SET @counter = RIGHT(@fullvar,3) + 1; -- still need to create this
You are viewing reply posted by: bigbear 18 days ago.
Posted 18 days ago

This is what I ended up using, I didn't try yours but yours looks good as well. I used a SEQUENCE in SQL Server.

create sequence dbo.ProjID
AS INTEGER
START WITH 10
INCREMENT BY 1;
GO

and to restart it at new year with a job

ALTER SEQUENCE dbo.ProjID
RESTART;

and the column in database using it

testVal nvarchar(10) default ( CONCAT( 'VSP', YEAR( GETDATE() ) % 100 , '-', 
    RIGHT( CONCAT('000', NEXT VALUE FOR dbo.ProjID), 3)))