文章/答案/技术大牛

发布

社区首页 >问答首页 >如何实现版本号列的自动递增？

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

Stack Overflow用户

提问于 2014-07-16 18:33:37

回答 2查看 3.5K关注 0票数 6

在Server中实现基于同一表中的另一个字段的自动递增的字段的好方法是什么？假设有一个VersionNumber字段，它为列DocumentID的每个值自动递增1到n。

要求：

VersionNumber必须从1开始，不能跳过任何数字。
必须持久化VersionNumber列。不能在视图中动态创建它，例如通过ROW_NUMBER。这样做的要点是创建审计跟踪。
理想情况下，可以防止VersionNumber的直接插入和更新。

详细

我正在设计一个需要版本数据的数据库。这类事情的基本结构是具有不变字段的标头表和包含以下内容的版本详细表：

标题表的外键
版本号
字段不能在不同版本之间更改。

“验证”数据的SQL策略和另一种用于验证数据的SQL策略等文章讨论了有效查询此类结构的策略，但没有讨论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 )
;

我的部分解决方案解决了在insert上递增版本号的问题，但不阻止对特定版本的选择性删除，也不阻止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;

sql-server

回答 2

Stack Overflow用户

发布于 2014-07-16 19:48:27

我很想在视野中计算一下。使用您可以使用的日期/id自动识别版本信息。

从VersionNumber中省略InvoiceVersion列，并加入一个日期时间w/默认值，并使用一个窗口函数为您计算版本(一旦舒服，就让它成为一个视图)。

InvoiceID与DateAdded对InvoiceVersion表的顺序分别与第二种情况下的InvoiceVersionID同时添加。

对删除内容的控制应该在前端执行，但可以通过在删除后触发的触发器执行，如果不是全部删除，则恢复数据。您可以从触发器中的已删除表中获取InvoiceID，如果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

票数 1

Stack Overflow用户

发布于 2014-07-16 20:36:04

这是一个我会尝试的选择。创建而不是触发器，以防止直接插入、更新或删除表InvoiceVersion。然后创建一个“插入”存储的proc，将新版本添加到InvoiceVersion表中。创建一个“删除”存储的proc，通过版本id删除发票的所有版本。每个proc将禁用相关触发器，执行所需的DML，然后启用触发器(全部在单个事务中)。

这对你有用吗？

--Don't allow inserts to InvoiceVersion.
CREATE TRIGGER insInvoiceVersion 
ON InvoiceVersion
INSTEAD OF INSERT
AS
BEGIN
    RAISERROR ('Direct inserts to table InvoiceVersion are not permitted.  Use stored proc InsertInvoiceVersion instead.', 16, 1);
END
GO

--Don't allow updates to InsertInvoiceVersion.
CREATE TRIGGER updInvoiceVersion 
ON InvoiceVersion
INSTEAD OF UPDATE
AS
BEGIN
    RAISERROR ('Direct updates of table InvoiceVersion are not permitted.', 16, 1);
END
GO

--Don't allow deletes from InsertInvoiceVersion.
CREATE TRIGGER delInvoiceVersion 
ON InvoiceVersion
INSTEAD OF DELETE
AS
BEGIN
    RAISERROR ('Direct deletes from table InvoiceVersion are not permitted.  Use stored proc DeleteInvoiceVersions instead.', 16, 1);
END
GO


CREATE PROCEDURE InsertInvoiceVersion
    @InvoiceID BIGINT
    --Other fields
AS
BEGIN
    --Get the next version number. (This could be replaced with a UDF for convenience, if desired.)
    DECLARE @VersionNumber INT

    SELECT @VersionNumber = MAX(VersionNumber) +1
    FROM InvoiceVersion
    WHERE InvoiceID = @InvoiceID

    --In case there are no existing versions of the invoice yet...
    SELECT @VersionNumber = COALESCE(@VersionNumber, 1)

    --Disable the insert trigger, insert, enable the insert trigger.
    ALTER TABLE InvoiceVersion DISABLE TRIGGER insInvoiceVersion

    INSERT INTO InvoiceVersion (InvoiceID, VersionNumber /*, other fields*/)
    VALUES (@InvoiceID, @VersionNumber /*, other fields*/)

    ALTER TABLE InvoiceVersion ENABLE TRIGGER insInvoiceVersion
END
GO

CREATE PROCEDURE DeleteInvoiceVersions
    @InvoiceID BIGINT
AS
    --Disable the delete trigger, delete, enable the delete trigger.
    ALTER TABLE InvoiceVersion DISABLE TRIGGER delInvoiceVersion

    DELETE FROM InvoiceVersion
    WHERE InvoiceID = @InvoiceID

    ALTER TABLE InvoiceVersion ENABLE TRIGGER delInvoiceVersion
GO

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/24788148

复制

相似问题

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

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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