# Sql-server – Create an incremental number with year and month without updating the entire table using a trigger

sql servertriggerupdate

# My situation and code

I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number. Something like this 2019.08.0001, 2019.08.0002, 2019.08.0003 etc. The ID I'll keep as internal PK for references from other tables.

For this I use this code.

create table Things (
ID int primary key identity not null,
Number nvarchar(20),
Text nvarchar(max),
CreationDate datetime not null
);

create trigger UpdateThingsNumberTrigger on Things
after insert
as
begin

declare @month nvarchar(2);
select @month = cast(month(getdate()) as nvarchar(2));

declare @code nvarchar(15);
select @code = cast(year(getdate()) as nvarchar(4)) +
'.' +
replicate('0', 2 - len(@month)) +
@month +
'.';

declare @max nvarchar(20);
select @max = t.ID
from Things t
where ID like @code + '%';

with CTE_UPD as
(
select
replicate('0',
4 -
len(cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)))) +
cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)) as NextNo,
ID
from Things ins
)
update Things
set Number = @code + NextNo
from Things t inner join CTE_UPD ins on ins.ID = t.ID;

end;

insert into Things (Text, CreationDate)
values ('A', current_timestamp), ('B', current_timestamp), ('C', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | ----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 2  | 2019.08.0002 | B    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157


But my trigger has a major logical flaw that has multiple effects… It never references the inserted virtual table. That means I'm updating the entire table every time when I insert a new row.

# Effects of the logical flaw

## When deleting a thing and inserting a new one

When I delete the row with ID 2, then the ID's after it, will change too.

delete Things
where ID = 2;

insert into Things (Text, CreationDate)
values ('D', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | -----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0002 | C    | 2019-08-23 08:54:15.157
-- 4  | 2019.08.0003 | D    | 2019-08-23 09:31:27.568


This isn't what I want to have. When ID 2 is removed, and a 4th is added, the numbers of things after ID 2 may not be changed. So the 4th thing must have number 2019.08.0004. Number 2019.08.0002 must be skipped. Like on table below.

ID | Number       | Text | CreationDate
-- | ------------ | ---- | -----------------------
1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157
4  | 2019.08.0004 | D    | 2019-08-23 09:31:27.568


## When it's another month

Also when it's another month, all the things will get an other number when inserting a new thing. Then, the output will be this, the expected results on the last column:

ID | Number       | Text | CreationDate            | ExpectedNumber
-- | ------------ | ---- | ----------------------- | --------------
1  | 2019.09.0001 | A    | 2019-08-23 08:54:15.157 | 2019.08.0001
3  | 2019.09.0002 | C    | 2019-08-23 08:54:15.157 | 2019.08.0003
4  | 2019.09.0003 | D    | 2019-08-23 09:31:27.568 | 2019.08.0004
5  | 2019.09.0004 | E    | 2019-09-01 10:34:54.059 | 2019.09.0001


# Question

How could I solve the logical flaw inside my trigger so that it has no effect to the existing things in my table?

Note: The flaw was shown on this question for Stack Overflow.

Schematically (no syntax):

CREATE TRIGGER UpdateThingsNumberTrigger
ON Things
AFTER INSERT
AS
WITH cte AS ( SELECT LEFT(Number, 8) yyyymm, CAST(RIGHT(MAX(Number),4) AS INT) nnnn
FROM Things
GROUP BY LEFT(Number, 8)
)
UPDATE Things
LEFT JOIN cte ON cte.yyyymm = YEAR(CreationDate)
+ '.'
+ MONTH(CreationDate)
+ '.'
SET Number = YEAR(CreationDate)
+ '.'
+ MONTH(CreationDate)
+ '.'
+ RIGHT('0000' + CAST((  COALESCE(nnnn, 0)
+ ROW_NUMBER() OVER (GROUP BY YEAR(CreationDate)
+ '.'
+ MONTH(CreationDate)
+ '.'
ORDER BY CreationDate) AS CHAR), 4)
WHERE Number IS NULL


Logic:

1. We update the whole table, but only those records which have no value in Number field (WHERE Number IS NULL).
2. In cte we obtain maximal current number for each year-month in the table divided to prefix (yyyymm) and number within group (nnnn).
3. For each group of records to be updated (1) we calculate new number part - we enumerate those records and add this number to nnnn part, then format in with leading zeros. If it is new year-month, no nnnn for it, and we set it to 0, so we start number part from 1.

PS. It is possible when the last record in the group by year-month was deleted - in such case deleted Number value will be re-assigned. I do not see the way to determine does this value existed previously and it was deleted, or it was not existed.