SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.YourTable (id int IDENTITY(1,1),
                            [type] int NOT NULL,
                            number int NULL,
                            [value] int NOT NULL,
                            IsDeleted bit NOT NULL CONSTRAINT DF_YourTable_IsDeleted DEFAULT 0);
CREATE VIEW dbo.YourTableView AS
    WITH CTE AS(
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY id ASC) AS Identifier,
               [type],
               number,
               [value],
               IsDeleted
        FROM dbo.YourTable)
    SELECT id,
           Identifier,
           [type],
           number,
           [value]
    FROM CTE
    WHERE IsDeleted = 0;
CREATE TRIGGER trg_YourTableView_Delete ON dbo.YourTableView 
INSTEAD OF DELETE AS
BEGIN
    SET NOCOUNT ON;
    UPDATE YT
    SET IsDeleted = 1
    FROM dbo.YourTable YT
         JOIN deleted d ON d.id = YT.id;
END;
INSERT INTO dbo.YourTableView (type,number,value)
VALUES(1,2,3),
      (4,5,6),
      (4,6,5);
3 rows affected

SELECT *
FROM dbo.YourTableView;


id
Identifier
type
number
value


1
1
1
2
3

2
1
4
5
6

3
2
4
6
5


UPDATE dbo.YourTableView
SET value = 7
WHERE [type] = 1
  AND Identifier = 1;
1 rows affected

DELETE 
FROM dbo.YourTableView
WHERE [type] = 4
  AND Identifier = 2;
1 rows affected

SELECT *
FROM dbo.YourTableView;


id
Identifier
type
number
value


1
1
1
2
7

2
1
4
5
6


INSERT INTO dbo.YourTableView (type,number,value)
VALUES(4,8,9); --Gets a value of 3, not 2
1 rows affected

SELECT *
FROM dbo.YourTableView;


id
Identifier
type
number
value


1
1
1
2
7

2
1
4
5
6

4
3
4
8
9


DROP VIEW dbo.YourTableView;
DROP TABLE dbo.YourTable;