<< SQL Server: Generating random data | Home | SQL Server: Inclusion of NULLs with UNPIVOT — Give your support >>
SQL Server: Fun with column-level auditing

Overview

Auditing the actual changes made to a table can be quite complex, but is often required.  In SQL Server 2008, you can use the new feature Change Data Capture, but in 2005 you have fewer options.

Here's an approach using common table expressions, table variables and a nice, big and hopefully fast query!

Context

click here to view this online and see the code formatted for viewing

Suppose you have a data table and an audit table as follows:

   1: CREATE TABLE tblToAudit (
   2:     pkid int identity(1,1) primary key,
   3:     col0 nvarchar(20),
   4:     col1 nvarchar(20),
   5:     col2 nvarchar(20),
   6:     col3 nvarchar(20),
   7:     col4 nvarchar(20),
   8:     col5 nvarchar(20),
   9:     col6 nvarchar(20),
  10:     col7 nvarchar(20),
  11:     col8 nvarchar(20),
  12:     col9 nvarchar(20),
  13:     lastModified datetime,
  14:     lastModifiedBy nvarchar(20)
  15:     )
  16: GO
  17: CREATE TABLE tblAudit (
  18:   object_name nvarchar(20) NOT NULL,
  19:   pkid int NOT NULL,
  20:   column_name nvarchar(20) NOT NULL,
  21:   prior_value nvarchar(20),
  22:   new_value nvarchar(20),
  23:   modified datetime,
  24:   modifiedBy nvarchar(20),
  25:   msg nvarchar(255)
  26: )
  27: GO

And now suppose you have some stored procedures that make changes to one or more rows, possibly also adding new rows (although this isn't particularly well suited to identity columns).  Each column value is passed to the stored procedure using a separate parameter.  How do you audit which columns have changed, and how have they changed?

One approach is to use successive IF ... THEN clauses, but this is slow and cannot be combined for multiple changes at once.

Solution

Here's my solution:

   1: BEGIN
   2:  
   3: SET NOCOUNT ON
   4:  
   5: -- Declare variables to represent the input data
   6: DECLARE @c0 nvarchar(20) = N'B', @c1 nvarchar(20) = N'C', @c2 nvarchar(20) = N'B',
   7:     @c3 nvarchar(20) = NULL, @c4 nvarchar(20) = N'D', @c5 nvarchar(20) = N'A',
   8:     @c6 nvarchar(20) = NULL, @c7 nvarchar(20) = NULL, @c8 nvarchar(20) = NULL,
   9:     @c9 nvarchar(20) = NULL, @user nvarchar(20) = N'Test',
  10:     @pkid int = 10
  11:  
  12: -- Create a table variable that is equivalent to the source table
  13: -- table variables are stored in tempdb so make sure we use this
  14: -- database's collation
  15: DECLARE @tgtData TABLE (
  16:     pkid int,
  17:     col0 nvarchar(20) COLLATE DATABASE_DEFAULT,
  18:     col1 nvarchar(20) COLLATE DATABASE_DEFAULT,
  19:     col2 nvarchar(20) COLLATE DATABASE_DEFAULT,
  20:     col3 nvarchar(20) COLLATE DATABASE_DEFAULT,
  21:     col4 nvarchar(20) COLLATE DATABASE_DEFAULT,
  22:     col5 nvarchar(20) COLLATE DATABASE_DEFAULT,
  23:     col6 nvarchar(20) COLLATE DATABASE_DEFAULT,
  24:     col7 nvarchar(20) COLLATE DATABASE_DEFAULT,
  25:     col8 nvarchar(20) COLLATE DATABASE_DEFAULT,
  26:     col9 nvarchar(20) COLLATE DATABASE_DEFAULT,
  27:     modified datetime,
  28:     modifiedBy nvarchar(20) COLLATE DATABASE_DEFAULT)
  29:  
  30: -- Insert new values into the table variable, can repeat multiple times
  31: INSERT INTO @tgtData VALUES (@pkid, @c0, @c1, @c2, @c3, @c4, @c5, @c6, 
                                   @c7, @c8, @c9, GETDATE(), @user)
  32:  
  33: -- This query eliminates NULLs as part of an aggregate so turn ANSI WARNINGS off
  34: SET ANSI_WARNINGS OFF;
  35:  
  36: -- Audit and change are one atomic operation, so need a transaction
  37: BEGIN TRAN;
  38:  
  39: -- Previous statement is completed with the required ;
  40: -- THIS IS THE AUDIT statement
  41: WITH 
  42: -- mergelist is a cartesian join of columns and affected row identifiers
  43: mergelist (pkid, colname) AS (
  44:     SELECT pkid, colname
  45:     FROM
  46:         -- Get a list of row identifiers which are being updated
  47:         (SELECT DISTINCT pkid FROM @tgtData) AS ids, 
  48:         -- Get a full list of columns
  49:         (    SELECT 'col0' AS colname UNION ALL
  50:             SELECT 'col1' AS colname UNION ALL
  51:             SELECT 'col2' AS colname UNION ALL
  52:             SELECT 'col3' AS colname UNION ALL
  53:             SELECT 'col4' AS colname UNION ALL
  54:             SELECT 'col5' AS colname UNION ALL
  55:             SELECT 'col6' AS colname UNION ALL
  56:             SELECT 'col7' AS colname UNION ALL
  57:             SELECT 'col8' AS colname UNION ALL
  58:             SELECT 'col9' AS colname
  59:         ) AS cols
  60: ),
  61: -- srcData is an unpivoted set of column values from the source table with NULLs missing
  62: srcData (pkid, colname, value) AS (
  63:     SELECT t.pkid, t.colname, t.value
  64:     FROM tblToAudit UNPIVOT (value FOR colname IN (col0, col1, col2, col3, col4, col5, 
                                                         col6, col7, col8, col9)) AS t
  65: ),
  66: -- tgtData is an unpivoted set of column values from the new data with NULLs missing
  67: tgtData (pkid, colname, value, modified, modifiedBy) AS (
  68:     SELECT t.pkid, t.colname, t.value, t.modified, t.modifiedBy
  69:     FROM @tgtData UNPIVOT (value FOR colname IN (col0, col1, col2, col3, col4, col5, 
                                                       col6, col7, col8, col9)) AS t
  70: ),
  71: -- changeList is the expanded set of srcData and tgtData including NULLs and with
  72: -- change indicating which values are NULL
  73: changeList (pkid, colname, old_value, new_value, change, modified, modifiedBy) AS (
  74:     SELECT mergelist.pkid, mergelist.colname, srcData.value, tgtData.value,
  75:         (2*COUNT(srcData.value) OVER (PARTITION BY mergelist.pkid, mergelist.colname)
  76:          + COUNT(tgtData.value) OVER (PARTITION BY mergelist.pkid, mergelist.colname))
  77:         AS change,
  78:         tgtData.modified, tgtData.modifiedBy
  79:     FROM mergeList 
  80:         LEFT JOIN srcData
  81:             ON mergelist.pkid = srcData.pkid AND mergelist.colname = srcData.colname
  82:         LEFT JOIN tgtData
  83:             ON mergelist.pkid = tgtData.pkid AND mergelist.colname = tgtData.colname
  84: )
  85: -- inserting into the audit table
  86: INSERT INTO tblAudit ([object_name], pkid, column_name, prior_value, new_value, 
                            modified, modifiedBy, msg)
  87: SELECT N'tblToAudit', pkid, colname, 
  88:     old_value, new_value, 
  89:     modified, modifiedBy,
  90:     -- use the change column to determine the action being taken
  91:     CASE change
  92:         WHEN 1 THEN N'set'
  93:         WHEN 2 THEN N'reset'
  94:         WHEN 3 THEN N'modify'
  95:     END AS msg
  96: FROM changeList 
  97: -- change = 0 means both source and target values are NULL
  98: WHERE changeList.change > 0 AND
  99: -- change = 3 means both source and target are NOT NULL, so need to compare them
 100:     (changeList.change < 3 OR (old_value <> new_value))
 101:  
 102: -- UPDATE existing rows
 103: UPDATE tblToAudit 
 104: SET 
 105:     tblToAudit.col0 = t.col0, 
 106:     tblToAudit.col1 = t.col1, 
 107:     tblToAudit.col2 = t.col2,
 108:     tblToAudit.col3 = t.col3,
 109:     tblToAudit.col4 = t.col4,
 110:     tblToAudit.col5 = t.col5,
 111:     tblToAudit.col6 = t.col6,
 112:     tblToAudit.col7 = t.col7,
 113:     tblToAudit.col8 = t.col8,
 114:     tblToAudit.col9 = t.col9,
 115:     tblToAudit.lastModified = t.modified,
 116:     tblToAudit.lastModifiedBy = t.modifiedBy
 117: FROM tblToAudit INNER JOIN @tgtData AS t ON tblToAudit.pkid = t.pkid
 118:  
 119: -- inserting identity column
 120: SET IDENTITY_INSERT tblToAudit ON
 121:  
 122: -- INSERT new rows (not inserting identity column)
 123: INSERT INTO tblToAudit (pkid, col0, col1, col2, col3, col4, col5, col6, 
                              col7, col8, col9, lastModified, lastModifiedBy)
 124: SELECT t.pkid, t.col0, t.col1, t.col2, t.col3, t.col4, t.col5, t.col6, 
             t.col7, t.col8, t.col9, t.modified, t.modifiedBy
 125: FROM @tgtData AS t LEFT JOIN tblToAudit ON t.pkid = tblToAudit.pkid
 126: WHERE tblToAudit.pkid IS NULL
 127:  
 128: -- reset identity insertion option
 129: SET IDENTITY_INSERT tblToAudit OFF
 130:  
 131: -- Commit the transaction
 132: COMMIT
 133:  
 134: END
 135: GO

I hope it is of some use to you.  If you want clarification of the code, or if you want to make a suggestion, please do send me a comment.

Versions

Metadata


Bookmark with :
Digg It! DZone StumbleUpon Technorati Reddit Del.icio.us Newsvine Furl Blinklist
posted @ Monday, March 10, 2008 12:35 PM | in SQL Server Software Development IT Management

Comments

No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  


Please add 7 and 4 and type the answer here: