Adam's profileAdam's spacePhotosBlogListsMore  |  Tools Help |
|
March 05 Adding auditing to a Sql 2005 databaseI needed to add an auditing feature to a project i'm working on. This auditing needs to store, in a seperate database, all of the inserts, updates and deletes that occur on the production database.
At first i created a seperate database using Sql Server Management Studio Express app calling it "MyProject_Audit" and started copying each table from the production database "MyProject" over to it. It quickly got boring so i thought i would look into using TSQL to do this - it got scary when i had to create the target tables and had to enumerated the source tables' columns.
It must be easier in C# using the Microsoft.SqlServer.Management.Smo namespace... it was!
The code below enumerates the source database, creating the target audit database, and also creates the INSERT, UPDATE, DELETE trigger on each of the source tables that will write the data to the audit tables.
Unfortunately, triggers can't access any columns that are Ntext, Text or Image so it doesn't attempt to deal with them - puts up a warning tho.
Also, there's a bug in SMO that doesn't allow you to create NVarChar(MAX) and VarChar(MAX) columns so I deal with them as a special case and make the audit table column the max values of 4000 and 8000 respectively.
It's a C# console app with references to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo, and Microsoft.SqlServer.SqlEnum.
using System.Diagnostics;using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlTypes;using Microsoft.SqlServer.Management.Smo;using Microsoft.SqlServer.Management.Common;namespace ViaReferral.Database.CreateAudit{ class Program{ #region Sql private const string TriggerSql = @"IF (SELECT COUNT(*) FROM inserted) > 0 BEGIN IF (SELECT COUNT(*) FROM deleted) > 0 BEGIN INSERT {0}..{1} (auditDateTime, auditAction, {2}) SELECT GETDATE(), 'U', {2} FROM inserted END ELSE BEGIN INSERT {0}..{1} (auditDateTime, auditAction, {2}) SELECT GETDATE(), 'I', {2} FROM inserted END END ELSE BEGIN INSERT {0}..{1} (auditDateTime, auditAction, {2}) SELECT GETDATE(), 'D', {2} FROM deleted END" ;#endregion static void Main(string[] args){ string sourceServerName = @"MAGNUM\SQLEXPRESS"; string targetServerName = @"MAGNUM\SQLEXPRESS"; string sourceDatabaseName = "MyProject"; string targetDatabaseName = "MyProject_Audit"; Server sourceServer = new Server(sourceServerName); if (sourceServer == null){ Console.WriteLine("SourceServer: " + sourceServerName + " can't be found"); return;} Server targetServer = new Server(targetServerName); if (targetServer == null){ Console.WriteLine("TargetServer: " + targetServerName + " can't be found"); return;} Microsoft.SqlServer.Management.Smo. Database sourceDatabase = sourceServer.Databases[sourceDatabaseName]; if (sourceDatabase == null){ Console.WriteLine("SourceDatabase: " + sourceDatabaseName + " can't be found"); return;} Microsoft.SqlServer.Management.Smo. Database targetDatabase = sourceServer.Databases[targetDatabaseName]; if (targetDatabase != null){ targetDatabase.Drop(); Console.WriteLine("TargetDatabase: " + targetDatabaseName + " dropped");} targetDatabase = new Microsoft.SqlServer.Management.Smo.Database(targetServer, targetDatabaseName);targetDatabase.Create(); foreach (Table sourceTable in sourceDatabase.Tables){ if (sourceTable.IsSystemObject == false){ List<string> columnNames; Table targetTable = CreateAuditTable(targetDatabase, sourceTable, out columnNames); Trigger auditTrigger = CreateAuditTrigger(sourceTable, targetTable, columnNames);} } } private static Table CreateAuditTable(Microsoft.SqlServer.Management.Smo.Database targetDatabase, Table sourceTable, out List<string> columnNames){ Table targetTable = new Table(targetDatabase, sourceTable.Name); Console.WriteLine("TargetTable: " + targetTable.Name);targetTable.Columns.Add( new Column(targetTable, "auditDateTime", DataType.DateTime));targetTable.Columns.Add( new Column(targetTable, "auditAction", DataType.NChar(1)));columnNames = new List<string>(); foreach (Column sourceColumn in sourceTable.Columns){ Console.Write("\t" + sourceColumn); if (sourceColumn.DataType.SqlDataType == SqlDataType.NText ||sourceColumn.DataType.SqlDataType == SqlDataType.Text ||sourceColumn.DataType.SqlDataType == SqlDataType.Image){ Console.Write(" ** Can't audit NText, Text or Image **");} else{ columnNames.Add(sourceColumn.Name); Column targetColumn = new Column(targetTable, sourceColumn.Name); // Handle VarCharMax and NVarCharMax data types if (sourceColumn.DataType.SqlDataType == SqlDataType.NVarCharMax){ targetColumn.DataType = new DataType(SqlDataType.NVarChar, 4000);} else if (sourceColumn.DataType.SqlDataType == SqlDataType.VarCharMax){ targetColumn.DataType = new DataType(SqlDataType.VarChar, 8000);} else{ targetColumn.DataType = sourceColumn.DataType; } targetColumn.Nullable = sourceColumn.Nullable; targetTable.Columns.Add(targetColumn); } Console.Write("\n");} targetTable.Create(); return targetTable;} private static Trigger CreateAuditTrigger(Table sourceTable, Table targetTable, List<string> columnNames) { string columnNamesParameter = string.Join(",", columnNames.ToArray()); string triggerName = sourceTable.Name + "_Audit"; Trigger auditTrigger = sourceTable.Triggers[triggerName]; if (auditTrigger != null){ auditTrigger.Drop(); } auditTrigger = new Trigger(sourceTable, triggerName);auditTrigger.TextMode = false;auditTrigger.Insert = true;auditTrigger.Update = true;auditTrigger.Delete = true;auditTrigger.ImplementationType = ImplementationType.TransactSql;auditTrigger.TextBody = string.Format(TriggerSql, targetTable.Parent.Name, targetTable.Name, columnNamesParameter);auditTrigger.Create(); Console.WriteLine("SourceTrigger: " + auditTrigger.Name + " on sourceTable " + sourceTable.Name); return auditTrigger;} } } |
|
|
