Detect Changes To A Database Table

• Download SQL Scripts - 2 Kb

Introduction

Detecting actual changes made to rows or columns in a database table, is a requirement that may be of much important to applications that require the tracking of updates made to database tables on a periodic basis or for auditing purposes. This article sorts to illustrate one of the ways by which this can be achieved i.e. through the use of available aggregate functions like BINARY_CHECKSUM and CHECKSUM_AGG.

Requirements

• Microsoft SQL Server 2000 with the Northwind sample database.
• SQL Query Analyzer provided with SQL Server 2000.

I. Using BINARY_CHECKSUM to detect any changes to the Rows of a Database table.

BINARY_CHECKSUM:

This aggregate function returns the binary checksum value computed over a row of a table or over a list of expressions. Its syntax is as follows:

Syntax:

BINARY_CHECKSUM ( * | expression [ ,...n ] ) 
<BR>

Arguments:

*
Specifies that the computation is over all the columns of the table.

Note: BINARY_CHECKSUM ignores columns of non-comparable data types in its computation. These data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.

expression

Is an expression of any type.

Note: BINARY_CHECKSUM ignores expressions of non-comparable data types in its computation. These data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.

Example 1:

The example given below uses the SHIPPERS table of the Northwind database to illustrate the use of the BINARY_CHECKSUM function to detect changes to Rows in a table. If you do not have access to the Northwind database table SHIPPERS, try using the SQL script provided in Appendix A to create the SHIPPERS table and insert 2 rows into it.

/* 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)

Give it a Quick Try: Copy the all SQL statements provided in steps 1 through 5 (given above) and execute it twice in SQL Query Analyzer. Verify the results returned, each time. It should return 2 rows with SHIPPERID values 1 and 2 after the first execution and no rows after the second. (See Appendix 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. Using CHECKSUM_AGG with BINARY_CHECKSUM to detect any changes to a Column of a Database table.

The syntax of the BINARY_CHECKSUM function has been already being provided in the previous example. Hence, only the

CHECKSUM_AGG 

function’s syntax is given below:

CHECKSUM_AGG:

This aggregate function returns the checksum of the values in a group as int. Null values are ignored. Its syntax is as follows:

Syntax:

CHECKSUM_AGG ( [ ALL | DISTINCT ] 
expression ) <BR>

Arguments:

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT 

Specifies that CHECKSUM_AGG return the checksum of unique values.

expression

Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the int data type. Aggregate functions and subqueries are not allowed.

Example 2:

The example given below uses the SHIPPERS table of the Northwind database to illustrate the use of the BINARY_CHECKSUM and CHECKSUM_AGG functions to detect changes to Columns in a table.

/* 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

Give it a Quick Try: Copy the all SQL statements provided in steps 1 through 4 (given above) and execute it twice in SQL Query Analyzer. Verify the results returned each time. It should return 2 different integer (checksum) values after the first execution and the same integer (checksum) values after the second execution. (See Appendix 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

Next Step:

The examples provided in this article, just serve the purpose of demonstrating the use of the CHECKSUM_AGG and BINARY_CHECKSUM aggregate functions in determining changes to rows and columns of a database table. They are very simple and straightforward. Please do go ahead and experiment further on the uses of these functions and extend the second example for modifications to more than one column. These functions can be used for auditing purposes (for example, updating the last updated date and time for a row that is actually modified), or solving data concurrency problems (for example, determining whether a row has been modified between selecting the row and updating it and raise a concurrency exception if it has).

Appendix A: CREATE SHIPPERS TABLE

If you plan to create your own table instead of using the Northwind Database table SHIPPERS, use the SQL script provided below. Also, in the examples provided above, specify the corresponding database used in the USE {Database} statements.

/* 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')

Appendix B: SNAPSHOT FOR EXAMPLE 1

Appendix C: SNAPSHOT FOR EXAMPLE 2

History

• Last Updated On 26th February, 2004.