sql-server - 如何将 rowversion 值存储在已有 rowversion 列的表中？

Question

我有两个表(Users、DeletedUsers)和一个触发器 Users_Delete。删除用户记录时，我想将 ID 和最后一个 rowversion 值(在删除之前)存储在 DeletedUsers 表中。

因为 SQL 不允许在一个表上有多个 rowversion 类型的列，我应该如何将 LastUserVersion 存储在 DeletedUsers 表中？我应该使用什么数据类型？

示例代码:

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

Answer 1

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

根据 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.

因为 [rowversion] 列的定义是 [Version] [rowversion] NOT NULL 您可以使用 BINARY(8)。