MS SQL Server 上的数据版本控制（使用通用触发器）

Martin Lachman

5.00/5 (4投票s)

2016年10月4日

CPOL

9分钟阅读

16262

415

如何在 MS SQL Server 中创建系统以检测数据库表中实际更改的记录。

引言

本文描述了一个简单紧凑的系统，用于检测数据库表中更改的记录。此解决方案的主要重点是检测实际更改的记录。本文可为想要制作更复杂的数据版本控制系统（如审计、历史记录核算等）的人提供启发。

背景

实际更改记录的问题

在考虑数据库系统（本例中为 MS SQL Server）中的通用数据版本控制 (DVC) 系统时，必须提出几个关键问题：

我们是否要监视所有被视为已更改的记录，还是只监视那些实际已更改的记录？
我们如何识别实际更改的记录？
谁负责识别实际更改的记录（某个表触发器、特殊存储过程等）？
我们应该如何标记某条记录已被实际更改？

MS SQL Server 没有提供自己的工具来确定受影响的记录是否已实际更改。如果受影响的记录包含在 update 语句的结果集中，则认为它已被更改 - 尽管记录的列中的所有数据与更新前相同。特别是，如果使用了 ROWVERSION 数据类型列，它可能在某些情况下有用，每次执行 update 语句时都会存储一个新值，我们无法对其进行任何干预。

有几种方法可以判断记录是否已被更改。例如：

通过比较每个 SQL UPDATE 语句中所有更改列的旧值和新值。这非常不方便且难以维护代码。但是，如果每个表只有一个 UPDATE 语句，则可以认为这是一种可接受的解决方案。
通过执行与点 1 类似的比较，使用表更新触发器。
计算行的校验和值（使用CHECKSUM 或BINARY_CHECKSUM 内置函数，它们返回一个 32 位整数），并将其与先前的值进行比较。我们可以使用星号而不是列列表。但是，校验和函数是为“...用于构建哈希索引”（正如您在 msdn 上看到的那样）而设计的。32 位整数值太小，无法避免冲突。并且它的实现存在一些特定问题 - 例如，NULL 值的使用等。
与点 3 类似的解决方案，这次使用内置哈希函数：HASHBYTES。该函数返回VARBINARY 值，其长度由所选哈希算法确定。对于 SHA 或 SHA1 算法，这是一个 160 位的值。然而，虽然并非完全不可能，但冲突几乎不可能发生，但并非不可能。第二个缺点是，不能在输入参数的位置使用列列表（或星号）。相反，必须使用连接函数，例如SELECT FOR XML 语句。

使用通用触发器的可能解决方案

此问题的解决方案基于通用的 SQL CLR 触发器。由于只有一个代码实现（针对所有表），因此可以称其为通用触发器。该触发器通过比较记录中每列的旧值和新值来识别实际更改的行。

接下来，每个 DVC 表都有一个特殊属性，其中包含一个行版本号。该编号是按顺序分配的整数值（由内置数据库序列生成器 - MS SQL Server 2012 及更高版本），它对给定数据库中的每行都是唯一的，并且在新行实际更改（或插入为新行）时会生成新值。实际更改的记录因此被标记。

我们的数据版本控制表具有这些系统属性：

系统列	数据类型	描述
`SysInsertRowVersion`	`BIGINT`	包含行插入时有效的唯一行版本值。该列有一个默认值约束 - 来自序列生成器的下一个值。
`SysUpdateRowVersion`	`BIGINT`	包含行版本的当前值 - 当行实际更改时，此处将存储一个新的唯一值。
`SysRowState`	`TINYINT`	指示行是否有效（即，已删除）。例如，值 0 表示行有效，而值 1 表示行已被删除。
`SysRowId`	`BIGINT`	这是实现为计算列的主键别名。它允许我们在旧表中使用原始主键列名，同时在通用触发器中使用通用名称。如果我们没有单列 PK（或 PK 基于非整数值），我们可以创建一个新列作为候选键（标识列）并将其声明为唯一键。
`SysTableName`	`VARCHAR(128)`	包含表名，作为对每行具有常量值的计算列。该值用于通用触发器。没有它，触发器将无法识别当前正在处理哪个表，也没有其他可靠的方法可以找出。计算列常量值不存储在表数据页中，不应太占用资源。

当记录插入到 DVC 表时，SysInsertRowVersion 列包含由序列生成器提供的新唯一值。该值由表上声明的默认约束强制执行。例如，如果我们存储先前处理任务的当前值，我们可以找出新插入的记录是否早于该任务完成的时间。

如果记录已被更新并且其某些列值已实际更改，则 SysUpdateRowVersion 列将显示由序列生成器给出新的唯一值，该值与 SysInsertRowVersion 列中的值不同。此值由通用更新触发器分配。

在这种情况下，使用 UPDATE 语句而不是 DELETE 语句来删除行。UPDATE 语句为 SysRowState 列赋一个表示“已删除记录”的值。为了简化 DVC 表的查询，可以为每个 DVC 表创建一个视图，排除已删除的行。

使用代码

下一部分将在单表简单案例的示例中描述该解决方案。问题可以分为 SQL Server 部分和 SQL CLR 部分（通用触发器实现）。

SQL Server 部分

首先，需要一个提供唯一长整型（BIGINT）递增值的序列生成器。在此示例中，它标记为seq_SysDVC，其 DDL 代码如下：

-- DVC System Sequencer:
CREATE SEQUENCE dbo.seq_SysDVC AS BIGINT
  START WITH 1
  INCREMENT BY 1
GO

其次，示例中的 DVC 表标记为TbCustomer，具有以下 DDL 代码：

-- sample customer table:
CREATE TABLE dbo.TbCustomer(
  -- custom PK column name
  CustomerId INTEGER NOT NULL IDENTITY(1,1),
  -- other business entity attributes:
  FirstName VARCHAR(100) NOT NULL,
  LastName VARCHAR(100) NOT NULL,
  Birthday DATETIME NOT NULL,
  Rating DECIMAL NOT NULL,
  -- DVC System Columns:
  SysInsertRowVersion BIGINT NOT NULL CONSTRAINT dfc_dbo_TbCustomer_SysInsertRowVersion DEFAULT NEXT VALUE FOR dbo.seq_SysDVC,
  SysUpdateRowVersion BIGINT NOT NULL CONSTRAINT dfc_dbo_TbCustomer_SysUpdateRowVersion DEFAULT -1,
  SysRowState TINYINT NOT NULL CONSTRAINT dfc_dbo_TbCustomer_SysRowState DEFAULT 0,
  SysRowId AS (CONVERT(BIGINT, CustomerId)),
  SysTableName AS ('dbo.TbCustomer'),
  -- native PK of the table
  CONSTRAINT pkc_TbCustomer PRIMARY KEY CLUSTERED (CustomerId ASC)
)
GO

在 SQL Server 端，需要一个由一个属性组成的通用表类型：SysRowId，用于提供通用触发器的批量更新语句。其 DDL 代码在此：

-- common table data type for a batch update statement of the generic trigger:
CREATE TYPE dbo.udt_DVCChangeResultTable AS TABLE(
  -- primary key
  SysRowId BIGINT NOT NULL PRIMARY KEY CLUSTERED
)
GO

SQL CLR 部分

在 SQL CLR 端，通用触发器有一个简单的程序集项目，其中包含一个公共类（CDVCTrigger），该类包含一个公共静态方法（OnUpdateBaseTrig），实现了其逻辑。该项目包含一些进一步的具有明显用途的辅助类和结构。

如果需要比较特定记录中给定列的旧值和新值，则必须使用两个触发器伪表（Inserted 和 Deleted）。然而，两个同时打开的 SqlDataReader 对象会导致触发器中使用上下文 SQL Server 连接时出现与“Multiple Active Result Sets”相关的问题，我们无法使用它们。为避免可能的资源分配增长，不应使用 DataSet/DataTable 对象。最后，为每个更改的行使用单独的命令会减慢触发器进程。因此，我使用自己的通用结构进行内存数据存储。

有关更多信息，请参阅 Visual Studio 项目，其中源代码提供了详细的解释。

创建一个 SQL CLR 项目的已编译二进制文件：DVC.SQLCLR.dll。可以通过以下方式将其部署到 SQL Server：

-- Allow CLR integration
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Drop the assembly if it already exists
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'DVC.SQLCLR')
  DROP ASSEMBLY [DVC.SQLCLR]
GO

-- Import the assembly - with set safe mode:
CREATE ASSEMBLY [DVC.SQLCLR] FROM 'C:\Temp\DVC.SQLCLR.dll' WITH PERMISSION_SET = SAFE
GO

将程序集的二进制文件复制到服务器端文件系统，例如复制到其本地路径“c:\Temp\”。
打开 SQL Server Management Studio，将上下文数据库切换到目标数据库，然后打开一个新的查询窗口。
在查询窗口中运行“安装”代码。

以下代码显示了如何简单地为上面示例中的表“声明”一个新的更新触发器（基于通用触发器）：

-- Drop the trigger when it already exists
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tri_TbCustomer_Upd') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER dbo.tri_TbCustomer_Upd
GO

-- Create the trigger - in better words: declare the trigger
CREATE TRIGGER dbo.tri_TbCustomer_Upd ON dbo.TbCustomer FOR UPDATE AS EXTERNAL NAME [DVC.SQLCLR].[DVC.SQLCLR.CDVCTrigger].OnUpdateBaseTrig
GO

现在任务已完成，是时候测试其结果了。

DVC 系统测试

示例表 TbCustomer 当前为空，必须插入新数据。并且可以通过以下方式获取 DVC 序列生成器的当前值：

-- This select returns current value of the DVC sequencer (for example, we can encapsulate it into a new function for 3rd party clients):
SELECT CONVERT(BIGINT, (SELECT TOP 1 T.Current_Value FROM SYS.Sequences AS T WHERE T.Name = 'seq_SysDVC'))

-- Insert sample records
INSERT INTO
  dbo.TbCustomer(FirstName, LastName, Birthday, Rating)
VALUES
  ('Graham', 'Greene', '1904-10-02', 50),
  ('Karel', 'Capek', '1890-01-09', 150),
  ('Josef', 'Skvorecky', '1924-09-27', 250)

-- Control select over the table
SELECT * FROM dbo.TbCustomer

如下所示，新记录在 SysInsertRowVersion 列中具有有效值。新值已由列默认约束在此处分配。

以下 UPDATE 语句会激活通用触发器。触发器会识别实际更改的记录并在 SysUpdateRowVersion 列中插入新值。

-- The update statement conditionally changes the rating of one record
UPDATE
  T
SET
  T.Rating = CASE WHEN T.CustomerId = 2 THEN (T.Rating * 2) ELSE T.Rating END
FROM
  dbo.TbCustomer AS T

-- Control select over the table
SELECT * FROM dbo.TbCustomer

尽管 SQL Server 返回“'(3 row(s) affected)'”消息，但只有一行实际上被更改了。

如 SysUpdateRowVersion 列所示，实际更改的记录通过数字的变化被标记，而未更改的记录保持未标记，从而证实了此解决方案区分已更新和实际更改数据的能力。

关注点

使用计算列别名代替原始主键列

如果对使用 SysRowId 列主键别名在其 WHERE 子句中的查询是否足够有效存在疑问，则可以比较以下两个由两个示例查询生成的执行计划：第一个查询使用本机主键列，而第二个查询使用别名列。执行计划不相同。

检测 SQL CLR 触发器当前上下文的父表对象

有几种“晦涩”的方法可以获取触发器触发的父表的名称。实际上，SqlTriggerContext 中没有属性可以找到这一点。有关更多详细信息，请参阅以下讨论主题：

历史

2016-10-04：第一个版本。