主页 / dba / 问题 / 343931
Accepted
Maury Markowitz
Asked: 2024-11-30 01:57:58 +0800 CST

在 SQL Server 上获取“事务时间”或类似内容以进行更改跟踪的好方法

  • 772

我想将版本信息添加到我们的数据库中 - 它的全部用途是检查用户读取行之后的版本是否已更改。我正在考虑为_VERSION所涉及的二十几个表添加一个名为的列。作为批处理UPDATE语句的一部分，我会使用类似于的内容SET ..._VERSION=something_or_other。我不知道此列包含哪些数据。

我见过的一个解决方案是使用 datetime 列，然后从 sys.dm_tran_active_transactions 获取事务开始时间，选择 的行CURRENT_TRANSACTION_ID()。对于我们的需求，1/300 的精度就足够了。我理解CURRENT_TIMESTAMP在这种情况下使用不太有用，因为这会随着批处理的进行而改变。

如果这是规范的话，我会这么做。这很容易实现。

但对于真正简单的用途来说，这是最好的方法吗？是否有其他值sys.dm_tran...可以提供相同的结果，但可能比日期时间更容易存储和定位？

注意：是的，我知道时间表和 MS 的变化跟踪，但被告知不要使用它们。

sql-server

3 个回答

  1. Best Answer

    检查用户读取某行数据后，该行的版本是否发生改变

    您不需要任何交易时间。

    为其引入了rowversion类型

    rowversion 数据类型只是一个递增的数字，不保留日期或时间。

    • 4

  2. 另一个答案建议rowversion在表中添加一列。这可以很好地工作，但如果您的表很大且事务性很高，您可能会不高兴，因为添加这些列可能会造成严重阻碍。据我所知，也没有很好的技巧或捷径可以解决此问题。

    您可能最好在表中添加一个具有默认值的非可空列（datetime2(0)这样就很好了），因为对于大多数现代 SQL Server 版本来说，这只是元数据的更改（与rowversion更改不同），并使用AFTER触发器来跟踪成功完成的语句。出于显而易见的原因，UPDATE您不需要它们进行INSERT或查询：DELETE

    • 任何插入都会获得SYSDATETIME()插入时的默认值
    • 物理删除（不是软删除）将完全删除该行

    使事情变得相当简单。

    脚本化

    对于列：

    ALTER TABLE
    dbo.Users
ADD
    LastModified datetime2(0) NOT NULL
    DEFAULT SYSDATETIME();

    对于触发器：

    CREATE OR ALTER TRIGGER
    dbo.User_Updates
ON dbo.Users
AFTER 
    UPDATE
AS
BEGIN 
    IF ROWCOUNT_BIG() = 0
    BEGIN
        RETURN;
        /*Bail early if no rows*/
    END;
    
    /*
    Set these in case they've been 
    altered in an outer context
    */
    SET NOCOUNT, XACT_ABORT ON;
    SET ROWCOUNT 0;

    IF UPDATE(LastModified)
    BEGIN
        RETURN;
        /*
        Bail if someone modifies 
        the column manually
        */
    END;
    
    /*Update any qualifying rows*/
    UPDATE
        u
    SET
        u.LastModified = SYSDATETIME()
    FROM dbo.Users AS u
    WHERE EXISTS
    (
        SELECT
            1/0
        FROM Inserted AS i
        WHERE u.Id = i.Id
    );
END;

    测试，1-2-3：

    BEGIN TRANSACTION
    SELECT
        u.Id,
        u.Age,
        u.LastModified
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
    
    UPDATE
        u
    SET
       u.Age = 138
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
    
    SELECT
        u.Id,
        u.Age,
        u.LastModified
    FROM dbo.Users AS u
    WHERE u.Id = 22656;
ROLLBACK TRANSACTION;

    结果：

    坚果

    对于触发器，您可以使用Inserted或Deleted虚拟表。这里这并不重要，因为它们都包含相同的基本行集。

    如果您希望使用之前的图片（Deleted表格）或之后的图片（Inserted表格）结果来审核实际更改的值，那么这很重要。

    • 4

  3. @MartinSmith 在 StackOverflow 上给出了一个精彩的答案，使用了 CDC 功能，但并没有真正将 CDC 应用到表中。

    CREATE TABLE dbo.YourTable
(  
    FooId INT PRIMARY KEY CLUSTERED,   
    FooName VARCHAR(50) NOT NULL,
    modstamp DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,   
    MaxDateTime2 DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (modstamp,MaxDateTime2)    
);

    它有一些局限性。

    • 存储的时间将由系统更新，并始终为 UTC。
    • 需要声明第二列（上面的 MaxDateTime2），但对于此用例来说，这完全是多余的。但可以将其标记为隐藏，以便更容易忽略。

    请注意，这里没有 Temporal Tables 历史表，它只是一对自动更新列。因此，如果您担心 Temporal Tables 的性能，那么这应该不是问题。

    • 1

相关问题