如何实现版本号列自动递增？

在 SQL Server 中实现基于同一个表中的另一个字段自动递增的字段的好方法是什么？想象一个VersionNumber自动递增的字段1 to n, 对于每个值列数DocumentID.

要求：

• VersionNumber 必须从 1 开始，并且不能跳过任何数字
• VersionNumber 列必须保留。它不能在视图中动态创建，例如viaROW_NUMBER。这样做的目的是创建审计跟踪。
• 理想情况下，将阻止直接插入和更新 VersionNumber

详细

我正在设计一个需要版本化数据的数据库。此类事情的基本结构是拥有一个包含不变字段的标头表和一个版本详细信息表，其中包含：

• 标头表的外键
• 版本号
• 字段可以随版本的不同而变化

文章如“版本化”数据的 SQL 策略 https://www.simple-talk.com/sql/database-administration/sql-strategies-for-versioned-data/ and 版本化数据的另一种 SQL 策略 https://www.simple-talk.com/sql/sql-training/yet-another-sql-strategy-for-versioned-data/讨论有效查询此类结构的策略，但不讨论该结构的实际实现VersionNumber领域（第二篇文章提供了部分示例，但为“高级练习”留下了更完整的实现）。

示例 DDL：

CREATE TABLE Invoice (
    InvoiceID   bigint identity(1,1) not null primary key,
    CreateDate  date not null
);

CREATE TABLE InvoiceVersion ( 
    InvoiceVersionID  bigint identity(1,1) not null primary key,
    InvoiceID         bigint not null,
    VersionNumber     int not null
    Other Fields...

    CONSTRAINT FK_InvoiceVersion_Invoice
    FOREIGN KEY ( InvoiceID ) 
    REFERENCES Invoice ( InvoiceID )
);

CREATE UNIQUE INDEX UQ_InvoiceVersion
    ON InvoiceVersion ( InvoiceID, VersionNumber )
;        

我的部分解决方案解决了插入时增加版本号的问题，但不阻止选择性删除特定版本，或阻止直接更新VersionNumber。不过，如果这一切不需要三个 50 行触发器，我会很高兴。

CREATE TRIGGER TRG_InvoiceVersion_Insert
    ON InvoiceVersion
INSTEAD OF INSERT
AS
BEGIN
    -- Prevent explicit VersionNumber insert
    DECLARE @ExplicitVersionCount int;

    SELECT @ExplicitVersionCount = COUNT(*)
    FROM inserted
    WHERE VersionNumber IS NOT NULL;

    IF (@ExplicitVersionCount > 0) BEGIN
        RAISERROR ('Explicit version number insert not allowed', 15, 1);
    END

    -- Get the current version number (implicit in row count)
    DECLARE @InsertingInvoiceID TABLE (
        InvoiceID bigint not null primary key 
    );

    DECLARE @CurrentVersions TABLE (
        InvoiceID bigint not null primary key,
        VersionCount int
    );

    INSERT INTO @InsertingInvoiceID ( InvoiceID )
    SELECT DISTINCT i.InvoiceID 
    FROM inserted i;

    INSERT INTO @CurrentVersions ( InvoiceID, VersionCount )
    SELECT I.InvoiceID, COUNT(V.InvoiceID)
    FROM @InsertingInvoiceID I
        LEFT JOIN migrate.InvoiceVersion V 
            ON V.InvoiceID = I.InvoiceID
    GROUP BY I.InvoiceID
    ;

    INSERT INTO InvoiceVersion (
        InvoiceID
      , VersionNumber
      , [Other fields...]
    )
    SELECT
        i.InvoiceID
      , v.VersionCount + ROW_NUMBER() OVER (PARTITION BY i.InvoiceID ORDER BY i.InvoiceID)
      , [Other fields...]
    FROM inserted i
        INNER JOIN @CurrentVersions v
            ON i.InvoiceID = v.InvoiceID
    ;
END;

我很想在视图中即时计算它。使用您可以使用的日期/ID 自动识别版本信息。

省略VersionNumber专栏来自InvoiceVersion，然后输入带有默认值的日期时间，并使用窗口函数为您计算版本（一旦舒服，就将其设为视图）。

分区由InvoiceID，并按以下顺序排列DateAdded to the InvoiceVersion表，以及InvoiceVersionID如果同时添加两个。

对删除内容的控制实际上应该在前端强制执行，但可以通过删除后触发的触发器来完成，并在未删除全部数据时恢复数据。你可以抓住InvoiceID(s) 从触发器中删除的表中，并且如果InvoiceID(s) 仍然存在于InvoiceVersion表，回滚并引发错误。

另一种选择不是实际删除，而是只是将“已删除”位列添加到Invoice桌子。将发票标记为“已删除”，但切勿将其从表中删除。那么在你看来，加入Invoice表，并添加where deleted=0.

希望这可以帮助。

查看示例：

IF OBJECT_ID('TEMPDB..#Invoice') IS NOT NULL DROP TABLE #Invoice
IF OBJECT_ID('TEMPDB..#InvoiceVersion') IS NOT NULL DROP TABLE #InvoiceVersion

CREATE TABLE #Invoice (
    InvoiceID   bigint identity(1,1) not null primary key,
    CreateDate  datetime not null
);

CREATE TABLE #InvoiceVersion ( 
    InvoiceVersionID  bigint identity(1,1) not null primary key,
    InvoiceID         bigint not null,
    Col1              varchar(100),
    Col2              int,
    DateAdded         datetime default CURRENT_TIMESTAMP
);

-- CREATE Invoice#1 & Invoice#2
INSERT INTO #Invoice (CreateDate) SELECT CURRENT_TIMESTAMP;
INSERT INTO #Invoice (CreateDate) SELECT CURRENT_TIMESTAMP;

-- CREATE 1 "version" for Invoice#1 & 4 for Invoice#2 & then 1 more for Invoice#1
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 1, 'TEST 1', 543
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 2, 'TEST 2', 34
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 2, 'TEST 2', 242
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 2, 'TEST 2', 965
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 2, 'TEST 2', 184
INSERT INTO #InvoiceVersion (InvoiceID, Col1, Col2) SELECT 1, 'TEST 1', 684

--SELECT * FROM #Invoice
--SELECT * FROM #InvoiceVersion

--CREATE VIEW VersionedInvoice
--AS
SELECT *, ROW_NUMBER() OVER(PARTITION BY InvoiceID ORDER BY InvoiceID, DateAdded, InvoiceVersionID) as VersionNumber
FROM #InvoiceVersion