Create customized auto increment Sequence number in SQL Server

Last Reply 2 months ago By bigbear

Posted 2 months 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
This question does not have replies that have been liked.