Detect Changes To A Database Table

/* 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 =
		TBLBINCHECK.SHIPPERID   AND BINARY_CHECKSUM(*) <> TBLBINCHECK.BCHECKSUM)