How to implement auto-incrementing version number column?

Question

What is a good way to implement a field in SQL Server that is auto-incrementing based on another field in the same table? Imagine a VersionNumber field that is auto-incrementing 1 to n, for each value of column DocumentID.

Requirements:

• VersionNumber must start at 1, and not skip any numbers
• The VersionNumber column must be persisted. It cannot be dynamically created in a view, such as via ROW_NUMBER. The point of this is creating an audit trail.
• Ideally, direct inserts and updates of VersionNumber would be prevented

In Detail

I'm designing a database that requires versioned data. The basic structure for this sort of thing is to have a header table with invariant fields and a version detail table with:

• A foreign key to the header table
• A version number
• Fields than can change from version to version

Articles such as SQL Strategies for 'Versioned' Data and Yet Another SQL Strategy for Versioned Data discuss strategies for efficiently querying this type of structure, but not the actual implementation of the VersionNumber field (the second article provides a partial example, but leaves a fuller implementation for 'an advanced exercise').

Example 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 )
;        

My partial solution addresses incrementing the version number on insert, but does not prevent selective deletes of specific versions, or prevent direct updates of VersionNumber. I'd love it, though, if all this didn't require three 50-line triggers.

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;

Answer 1

I would be tempted to calculate it on the fly in a view. Use the dates/id's at your disposal to identify version info automatically.

Omit the VersionNumber column from InvoiceVersion, and toss in a datetime w/a default value, and use a windowing function to calculate the version for you (once comfortable, make it a view).

Parition by the InvoiceID, and order by the DateAdded to the InvoiceVersion table, and the InvoiceVersionID in case two are added simultaneously.

Control over what is deleted should really be enforced on the front end, but could be done via a trigger that fires after delete, and restores the data if not all are deleted. You could just grab the InvoiceID(s) from the deleted table in the trigger, and if the InvoiceID(s) still exists in the InvoiceVersion table, rollback and raise error.

Another option instead of actually deleting, is just adding a "deleted" bit column to the Invoice table. Mark an invoice as "deleted", but never delete it from the table. Then in your view, join the Invoice table, and add where deleted=0.

Hope this helps.

View Example:

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

Answer 2

Here's an option I would try. Create INSTEAD OF triggers to prevent direct inserts, updates, or deletes on table InvoiceVersion. Then create an "insert" stored proc that adds new versions to the InvoiceVersion table. Create a "delete" stored proc that deletes all versions of an invoice by the version id. Each proc will disable the related trigger, perform the required DML, then enable the trigger (all within a single transaction).

Could this work for you?

--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