Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 2nd, 2009, 02:11 AM
Registered User
 
Join Date: Nov 2004
Location: Ahmedabad, Gujrat, India.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to maulik77 Send a message via Yahoo to maulik77
Default Trigger-based auditing to your SQL Server database

How do you track changes to data in your database? There are a variety of supported auditing methods for SQL Server, including comprehensive C2 security auditing, but what do you do if you're solving a business rather than a security problem, and you're interested in tracking the following kinds of information:
  • What data has been updated recently
  • Which tables have not been updated recently
  • Who modified the price of Steeleye Stout to $20 / unit, and when did they do it?
  • What was the unit price for Steeleye Stout before Jon monkeyed with it?
There are a number of ways to design this into your solution from the start, for example:
  • The application is designed so that all changes are logged
  • All data changes go through a data access layer which logs all changes
  • The database is constructed in such a way that logging information is included in each table, perhaps set via a trigger
What if we're not starting from scratch?

But what do you do if you need to add lightweight auditing to an existing solution, in which data can be modified via a variety of direct access methods? When I ran into that challenge, I decided to use Nigel Rivett's SQL Server Auditing triggers. I read about some concern with the performance impact, but this database wasn't forecasted to have a high update rate. Nigel's script works by adding a trigger for INSERT, UPDATE, and DELETE on a single table. The trigger catches data changes, then saves out the information (such as table name, the primary key values, the column name that was altered, and the before and after values for that column) to an Audit table.
I needed to track every table in the database, though, and I expected the database schema to continue to change. I was able to generalize the solution a bit, because the database convention didn't use any no compound primary keys. I created the script listed below, which loops through all tables in the database with the exception of the Audit table, of course, since auditing changes to the audit table is both unnecessary and recursive. I'm also skipping sysdiagrams; you could include any other tables you don't want to track to that list as well.
The nice thing about the script I'm including below is that you can run it after making some schema changes and it will make sure that all newly added tables are included in the change tracking / audit, too.
Here's an example of what you'd see in the audit table for an Update followed by an Insert. Notice that the Update shows type U and a single column updated, while the Insert (type I) shows all columns added, one on each row:
http://farm3.static.flickr.com/2342/...23122640_o.png

While this information is pretty unstructured, it's not difficult to run some useful reports. For instance, we can easily find things like
  • which tables were updated recently
  • which tables have not been updated in the past year
  • which tables have never been updated
  • all changes made by a specific user in a time period
  • most active tables in a time period
While it's not as easy, it's possible to backtrack from the current state to determine the state of a row in a table at a certain point in time. It's generally possible to dig out the state of an entire table at a point in time, but a change table isn't a good a fit for temporal data tracking - the right solution there is to start adding Modified By and Modified On columns to the required tables.
Note that we're only tracking data changes here. If you'd like to track schema changes, take a look at SQL Server 2005's DDL triggers.
Enough talking, give us the script!

Sure. I'll repeat that there are some disclaimers to the approach - performance, it'll only track changes to tables with a primary key, etc. If you want to know more about the trigger itself, I'd recommend starting with Nigel's article. However, it worked great for our project.
USE MYAWESOMEDATABASE
GO

IFNOTEXISTS(SELECT*FROMINFORMATION_SCHEMA.TABLESWHERE TABLE_NAME='Audit')
CREATETABLE Audit
(
AuditID [int]IDENTITY(1,1)NOTNULL,
Typechar(1),
TableName
varchar(128),
PrimaryKeyField
varchar(1000),
PrimaryKeyValue
varchar(1000),
FieldName
varchar(128),
OldValue
varchar(1000),
NewValue
varchar(1000),
UpdateDate
datetimeDEFAULT(GetDate()),
UserName
varchar(128)
)
GO

DECLARE @sql varchar(8000), @TABLE_NAMEsysname
SET
NOCOUNTON

SELECT
@TABLE_NAME=MIN(TABLE_NAME)FROMINFORMATION_SCHEMA.TablesWHERE
TABLE_TYPE
='BASE TABLE'AND TABLE_NAME!='sysdiagrams'
AND TABLE_NAME!='Audit'

WHILE @TABLE_NAMEISNOTNULL
BEGIN
EXEC('IF OBJECT_ID ('''+ @TABLE_NAME+'_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER '+ @TABLE_NAME+'_ChangeTracking')
SELECT @sql ='
create trigger '
+ @TABLE_NAME+'_ChangeTracking on '+ @TABLE_NAME+' for insert, update, delete
as

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = '''
+ @TABLE_NAME+'''

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + '' is null)''
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME=MIN(TABLE_NAME)FROMINFORMATION_SCHEMA.TablesWHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE='BASE TABLE'AND TABLE_NAME!='sysdiagrams'
AND TABLE_NAME!='Audit'
END


Have a Happy Coding...
Author: Jon Galloway
Refrence : http://weblogs.asp.net/jgalloway/arc...-database.aspx


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trigger Problem in sql server monika.vasvani SQL Language 1 March 1st, 2007 07:09 AM
Using SQL Server-based cache invalidation mechanis michurin ASP.NET 2.0 Basics 0 January 25th, 2007 04:43 PM
Trigger in SQL Server 2000 deniscuba SQL Server 2000 4 April 1st, 2005 11:37 AM
SQl Server 2000 Trigger everest SQL Server 2000 5 April 26th, 2004 01:44 AM
Can we stop to fire trigger in SQL Server? vincentc SQL Server 2000 2 November 18th, 2003 12:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.