检测数据库表的变化

Pradeep Fernandes

2.63/5 (21投票s)

2004年2月26日

4分钟阅读

91002

812

本文旨在说明通过使用 BINARY_CHECKSUM 和 CHECKSUM_AGG 等可用聚合函数来检测数据表更改的一种方法。

下载 SQL 脚本 - 2 Kb

引言

检测数据库表中行或列的实际更改，对于需要定期跟踪数据库表更新或出于审计目的的应用程序来说，可能是一项非常重要的需求。本文旨在说明实现此目的的一种方法，即通过使用 BINARY_CHECKSUM 和 CHECKSUM_AGG 等可用聚合函数。

要求

Microsoft SQL Server 2000 和 Northwind 示例数据库。
SQL Server 2000 提供的 SQL Query Analyzer。

I. 使用 BINARY_CHECKSUM 检测数据库表行中的任何更改。

BINARY_CHECKSUM:

此聚合函数返回在表行或表达式列表上计算的二进制校验和值。其语法如下：

语法

BINARY_CHECKSUM ( * | expression [ ,...n ] )
参数

*
指定计算针对表的所有列。

注意： BINARY_CHECKSUM 在计算中会忽略不可比较数据类型的列。这些数据类型包括 text、ntext、image 和 cursor，以及以上述类型之一为基类型的 sql_variant。

expression

是任何类型的表达式。

注意： BINARY_CHECKSUM 在计算中会忽略不可比较数据类型的表达式。这些数据类型包括 text、ntext、image 和 cursor，以及以上述类型之一为基类型的 sql_variant。

示例 1

下面的示例使用 Northwind 数据库的 SHIPPERS 表来说明 BINARY_CHECKSUM 函数在检测表中行更改时的用法。如果您无法访问 Northwind 数据库表 SHIPPERS，请尝试使用附录 A 中提供的 SQL 脚本来创建 SHIPPERS 表并插入 2 行数据。

/* Step 1: Use the Northwind Database*/

USE NORTHWIND
GO

/* Step 2: Check if the Table TBLBINCHECK exists. If so, drop it. */

-- Check if the Table TBLBINCHECK exists
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = 
OBJECT_ID(N'TBLBINCHECK')AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
-- Drop it if, it exist already
DROP TABLE TBLBINCHECK
GO 

/* Step 3: Create the Table TBLBINCHECK to hold the checksum value before
the values in the specific rows
i.e. SHIPPERID = 1 and 2 are changed. 
Note: Ensure there are rows in the SHIPPERS Table with SHIPPERID values 1 
and 2. If not, insert them.*/

CREATE   TABLE TBLBINCHECK (SHIPPERID INT, BCHECKSUM INT)
INSERT   INTO TBLBINCHECK
         SELECT   SHIPPERID, BINARY_CHECKSUM(*)
         FROM      SHIPPERS

/* Step 4: Modify the values of the COMPANYNAME field in the SHIPPERS 
table for the rows with SHIPPERID field values 1 and 2.*/

UPDATE SHIPPERS SET COMPANYNAME = 'United Couriers' WHERE SHIPPERID = 1
UPDATE SHIPPERS SET COMPANYNAME = 'DHL', PHONE = '2345673' WHERE
 SHIPPERID = 2

/* Step 5: Determine which rows of the SHIPPERS table have been modified. 
Obtain the corresponding values of the SHIPPERID field. These values can be
used as needed, say, for auditing purposes. */
SELECT 
  SHIPPERID FROM
  TBLBINCHECK   WHERE   EXISTS
  (SELECT   SHIPPERID
     FROM      SHIPPERS
            WHERE SHIPPERS.SHIPPERID =
      BLBINCHECK.SHIPPERID AND BINARY_CHECKSUM(*) <> BLBINCHECK.BCHECKSUM)

快速尝试： 复制以上步骤 1 至 5 中提供的所有 SQL 语句，并在 SQL Query Analyzer 中执行两次。每次都验证返回的结果。第一次执行后，应返回 SHIPPERID 值为 1 和 2 的 2 行；第二次执行后，不应返回任何行。（参见附录 B）。

Results:


a. After the first execution of the SQL Statements provided in steps 1 
through 5.

Result:

SHIPPERID
1
2

b. After the second execution of the SQL Statements provided in steps 1 
through 5.

Result:

SHIPPERID

II. 使用 CHECKSUM_AGG 和 BINARY_CHECKSUM 来检测数据库表列中的任何更改。

BINARY_CHECKSUM 函数的语法已在前一个示例中提供。因此，下面仅给出 CHECKSUM_AGG 函数的语法：

CHECKSUM_AGG:

此聚合函数返回组中值的校验和（int 类型）。NULL 值将被忽略。其语法如下：

语法

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )
参数

ALL

将聚合函数应用于所有值。ALL 是默认选项。

DISTINCT

指定 CHECKSUM_AGG 返回唯一值的校验和。

expression

是常量、列或函数，以及算术、按位和字符串运算符的任意组合。expression 是 int 数据类型的表达式。不允许使用聚合函数和子查询。

示例 2：

下面的示例使用 Northwind 数据库的 SHIPPERS 表来说明 BINARY_CHECKSUM 和 CHECKSUM_AGG 函数在检测表中列更改时的用法。

/* Step 1: Use the Northwind Database*/

USE   Northwind
GO

/* Step 2: Get the checksum of the Column - COMPANYNAME, 
which will be modified */

SELECT   CHECKSUM_AGG(BINARY_CHECKSUM(COMPANYNAME)) AS CHKSUMVALUE FROM 
  SHIPPERS

/* Step 3: Change the values of a row for the Column – COMPANYNAME. 
Note: Ensure there is a row in the SHIPPERS Table with SHIPPERID value as 2.
If not, insert a row. */

UPDATE SHIPPERS SET COMPANYNAME = 'SAFE EXPRESS' WHERE SHIPPERID = 2
 
/* Step 4: Get the checksum of the MODIFIED Column - COMPANYNAME after 
updates to the SHIPPERS Table */ 

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(COMPANYNAME)) AS CHKSUMVALUE   FROM
  SHIPPERS

快速尝试： 复制以上步骤 1 至 4 中提供的所有 SQL 语句，并在 SQL Query Analyzer 中执行两次。每次都验证返回的结果。第一次执行后，应返回 2 个不同的整数（校验和）值；第二次执行后，应返回相同的整数（校验和）值。（参见附录 C）。

Results:
a. After the first execution of the SQL Statements provided in steps 1
through 4.

Result: 2 different integer values, say, 

CHKSUMVALUE
643608494 

CHKSUMVALUE
1804463772

b. After the second execution of the SQL Statements provided in steps 1
through 4.       

Result: 2 identical integer values, say, 

CHKSUMVALUE
1804463772

CHKSUMVALUE
1804463772

下一步：

本文提供的示例仅用于演示 CHECKSUM_AGG 和 BINARY_CHECKSUM 聚合函数在确定数据库表行和列更改方面的用法。它们非常简单明了。请您继续深入探索这些函数的用法，并扩展第二个示例以用于修改多个列。这些函数可用于审计目的（例如，更新实际修改过的行的最后更新日期和时间），或解决数据并发问题（例如，确定一行在选择该行和更新该行之间是否已被修改，并在已修改的情况下引发并发异常）。

附录 A：创建 SHIPPERS 表

如果您计划创建自己的表而不是使用 Northwind 数据库表 SHIPPERS，请使用下面提供的 SQL 脚本。此外，在上面的示例中，请在 USE {Database} 语句中指定相应的数据库。

/* SQL Script to create the SHIPPERS Table */
/* Check if the SHIPPERS Table exists, if so drop it */

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = 
OBJECT_ID(N'[DBO].[SHIPPERS]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[SHIPPERS]
GO

/* Create the SHIPPERS Table */

CREATE TABLE [DBO].[SHIPPERS] (
     [SHIPPERID] [INT] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
         [COMPANYNAME] [NVARCHAR] (40) NOT
     NULL, [PHONE] [NVARCHAR] (24)
     NULL
                              )

GO
 
/* Create two rows in the SHIPPERS Table */
 
INSERT INTO SHIPPERS (COMPANYNAME, PHONE) 
            VALUES ('PROFESSIONAL COURIERS', '3434344') 
INSERT INTO SHIPPERS (COMPANYNAME, PHONE) 
            VALUES ('SEVEN SEAS', '4534535')

附录 B：示例 1 的快照

附录 C：示例 2 的快照

历史

最后更新日期：2004 年 2 月 26 日。