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 4^th is added, the numbers of things after ID 2 may not be changed. So the 4^th 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.}