CODEWITHSUNDEEP
sql-serverrowversion
Gene C
Gene C

Reputation: 2030

How do I store a rowversion values in a table that already has a rowversion column?

I have a two tables (Users, DeletedUsers) and a trigger Users_Delete. When a user record is deleted I want to store the id and the last rowversion value (before it was deleted) in the DeletedUsers table.

Because SQL does not allow multiple columns of type rowversion on a table, how should I store the LastUserVersion in the DeletedUsers table? What datatype should I use?

Sample Code:

CREATE TABLE [dbo].[Users] (
    [ID] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [Version] [rowversion] NOT NULL
)

CREATE TABLE [dbo].[DeletedUsers] (
    [ID] [uniqueidentifier] NOT NULL,
    [LastUserVersion] [rowversion] NOT NULL,    -- ERROR
    [Version] [rowversion] NOT NULL
)

CREATE TRIGGER [dbo].[Users_Delete] ON [dbo].[Users]
    AFTER DELETE
    AS
    BEGIN
    SET NOCOUNT ON

    INSERT INTO [dbo].[DeletedUsers]
    (
        ObjectID,
        LastUserVersion
    )
    SELECT
        ObjectID,
        Version
    FROM DELETED
    SET NOCOUNT OFF
    END

Upvotes: 3

Views: 3811

Answers (2)

Martin K.
Martin K.

Reputation: 1060

I don't really see a point in keeping a rowversion of a deleted row. Besides, you are not only limited to one rowversion column per table, you are also not allowed to insert or update that column.

That said, rowversion is Binary(8), so you can use that. Basically, it is a number, so you can convert it to an integer which may be more convenient. For 8 Byte, you will need bigint.

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

How do I store a rowversion values in a table that already has a rowversion column?

According to MSDN:

A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

Because the definition of [rowversion] column is [Version] [rowversion] NOT NULL you could use BINARY(8).

Upvotes: 3

Related Questions