Click here to Skip to main content
Click here to Skip to main content

Detect Changes To A Database Table

, 25 Feb 2004
Rate this:
Please Sign up or sign in to vote.
This article sorts to illustrate one of the ways by which data table changes can be detected through the use of available aggregate functions like BINARY_CHECKSUM and CHECKSUM_AGG.

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 ] )

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 )

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Pradeep Fernandes
Web Developer
India India
MCSD in .NET,
WIPRO TECHNOLOGIES,
BANGALORE, INDIA

Comments and Discussions

 
GeneralMy vote of 1 PinmemberProject-A3-Jun-10 3:12 
GeneralGood article in the original form Pinmemberscanner2001129-Apr-04 12:35 
QuestionWhy not using timestamp datatype? PinmemberAttila Hajdrik2-Mar-04 11:53 
AnswerRe: Why not using timestamp datatype? PinmemberSébastien Lorion3-Mar-04 3:13 
GeneralReformat Pinmemberm a y s a m26-Feb-04 18:34 
Dear sir
Consider reformatting your article. Upload pictures with less width so that they fit in every resolution.
Thanks


Don't forget, that's
Persian Gulf
not Arabian gulf!


Murphy:Click Here![^]

Events and Delegates simplified:Click Here![^]

I'm thirsty like sun, more landless than wind...

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 26 Feb 2004
Article Copyright 2004 by Pradeep Fernandes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid