By David Ziffer
with technical assistance from Jeff Farris
Copyright 2005-2006 by David Ziffer, All Rights Reserved.
If you are implementing an IT project of any significant size, then auditing is probably an important requirement of that project. Standard auditing usually includes tracking:
• insertions of new records,
• deletions of existing records,
• and modifications of fields in existing records.
A typical audit table looks like this:
| Audit-Id | Table | Row-Id | Operation | Occurred-At | Performed-By | Field | Old-Value | New-Value |
| 1 | NAME | 2 | UPDATE | 2005-09-26 11:06:01.613 | dbo | First | Moe | Curly |
| 2 | ADDR | 14 | INSERT | 2005-09-26 11:08:02.790 | dbo | NULL | NULL | NULL |
| 3 | ADDR | 13 | DELETE | 2005-09-26 11:08:12.023 | dbo | NULL | NULL | NULL |
For the sake of simplicity this example assumes a database in which all tables have an auto-increment primary key field, which makes auditing simpler (you can easily modify the example code below to handle more general cases). The columns of this audit table are as follows:
1. Audit-Id: the unique key of the audit table
2. Table: the name of the table in which the audited event occurred
3. Row-Id: the unique key of the row of the table in which the audited event occurred
4. Operation: the type of operation (update, insert, delete)
5. Occurred-At: the date/time at which the event occurred
6. Performed-By: the database ID of the user who caused the event
7. Field: the name of the specific field in which an update occurred (for updates only)
8. Old-Value: the value in the field before the update
9. New-Value: the value in the field after the update
The table looks simple but producing it isn't. To monitor every table in a database formerly required that you either create a custom trigger for each table, or that you generate an SQL string dynamically and "exec" it. This is due to the fact that Transact-SQL cannot use variables as substitutes for database elements such as table names and column names: a Transact-SQL audit trigger must either hard-code the names (in which case you need one routine per table) or it must construct an SQL string containing the proper names at run time. Thus despite the fact that auditing is a relatively mundane and repetitive chore, it was impossible to write a fast, practical generic routine that could function as a trigger for every table.
The new CLR integration in SQL Server 2005 changes all that because it places the full power of the CLR languages at your disposal. Without the restrictions of Transact-SQL, you can easily write a routine that can function as a trigger for every table in your database. The only per-table code you will need is a single declarative statement that associates your single CLR trigger routine with each table. That's one line of code per table rather than dozens of lines of custom code to be maintained with every table change. If you have a substantial project, that could mean a savings of dozens or perhaps even hundreds of man-hours per year.
Below is a generic CLR routine that populates an audit table having the structure shown in the example above. We will use it to produce an assembly called "AuditCommon". To produce the assembly using Visual Studio 2005:
- create a new VB / Database / SQL Server project called "AuditCommon" (this implicitly also creates a solution called "AuditCommon");
- when prompted, associate the project with an empty database on some available SQL Server 2005 instance;
- add a new "Trigger" item called "AuditCommon.vb";
- copy the following code into the "AuditCommon.vb" module, completely overwriting the original template code that is automatically supplied in the file:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Module Common
' COMMON STRINGS
Friend Const CONTEXT_CONNECTION_STRING As String = "context connection=true"
' AUDIT-RELATED STRINGS
Friend Const AUDIT_TABLENAME As String = "AUDIT"
Friend Const AUDIT_OPCODE_DELETE As String = "D"
Friend Const AUDIT_OPCODE_INSERT As String = "I"
Friend Const AUDIT_OPCODE_UPDATE As String = "U"
' COMMON FUNCTIONS
''' <summary>
''' Derive a table's name from its primary key name.
''' </summary>
''' <remarks>
''' SQL CLR does not deliver the proper table name from either InsertedTable.TableName
''' or DeletedTable.TableName, so we must use a substitute based on our key naming
''' convention. We assume that in each table, the KeyFieldName = TableName + "Id".
''' Remove this routine and its uses as soon as we can get the table name from the CLR.
''' </remarks>
Friend Function DeriveTableNameFromKeyFieldName(ByVal KeyFieldName As String) As String
Return Left(KeyFieldName, Len(KeyFieldName) - 2).ToUpper() ' assumes KeyName = TableName & "Id"
End Function
''' <summary>
''' Emit debug messages to the "immediate" window in the Visual Studio IDE.
''' </summary>
''' <remarks>
''' WARNING: the Pipe object is not available in user functions, so don't use this function from within one.
''' </remarks>
#If DEBUG Then
Friend Sub EmitDebugMessage(ByVal Message As String)
SqlContext.Pipe.Send(Message)
End Sub
#End If
End Module
Partial Public Class Triggers
' GENERIC AUDIT TRIGGER: AuditCommon
''' <summary>
''' Generic Audit Trigger function.
''' </summary>
''' <remarks>
''' Note that the "SqlTrigger" attrbute does not contain one of its normal tags; namely it does not
''' specify any particular table. We don't know if it is Microsoft's intention to allow table-agnostic
'''trigger code, but this works and we hope that it keeps working.
''' </remarks>
<Microsoft.SqlServer.Server.SqlTrigger(Name:="AuditCommon", Event:="FOR UPDATE, INSERT, DELETE")> _
Public Shared Sub AuditCommon()
' Define some constants.
Const ROW_ZERO As Integer = 0
Const COLUMN_ZERO As Integer = 0
Try
#If DEBUG Then
EmitDebugMessage("Enter Trigger")
#End If
' Grab the already-open Connection to use as an argument
#If DEBUG Then
EmitDebugMessage("Open Connection")
#End If
Dim Connection As New SqlConnection(CONTEXT_CONNECTION_STRING)
Connection.Open()
' Load the "inserted" table
#If DEBUG Then
EmitDebugMessage("Load INSERTED")
#End If
Dim TableLoader As New SqlDataAdapter("select * from inserted", Connection)
Dim InsertedTable As New Data.DataTable
TableLoader.Fill(InsertedTable)
' Load the "deleted" table
#If DEBUG Then
EmitDebugMessage("Load DELETED")
#End If
TableLoader.SelectCommand.CommandText = "select * from deleted"
Dim DeletedTable As New Data.DataTable
TableLoader.Fill(DeletedTable)
' Prepare the "audit" table for insertion
#If DEBUG Then
EmitDebugMessage("Load AUDIT schema for insertion")
#End If
Dim AuditAdapter As New SqlDataAdapter("select * from " & AUDIT_TABLENAME & " where 1 = 0", Connection)
Dim AuditTable As New Data.DataTable
AuditAdapter.FillSchema(AuditTable, SchemaType.Source)
Dim AuditCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(AuditAdapter)
' Based on the type of event, determine how many rows to look at.
Dim Context As SqlTriggerContext = SqlContext.TriggerContext()
Dim RowLimit As Integer = -1
Select Case Context.TriggerAction
Case TriggerAction.Update, TriggerAction.Insert
' Insert and Update should both have populated the Inserted table
RowLimit = InsertedTable.Rows.Count - 1
Case TriggerAction.Delete
' Delete should have populated the Deleted table
RowLimit = DeletedTable.Rows.Count - 1
End Select
#If DEBUG Then
EmitDebugMessage("Create internal representations of trigger table rows")
#End If
' Create DataRow objects corresponding to the trigger table rows.
Dim TableName As String = ""
For RowIndex As Integer = ROW_ZERO To RowLimit
Dim InsertedRow As Data.DataRow = Nothing
If InsertedTable.Rows.Count > RowIndex Then
InsertedRow = InsertedTable.Rows(RowIndex)
' FUTURE: Use "InsertedTable.TableName" when Microsoft fixes the CLR to supply it.
If TableName = "" Then
TableName = DeriveTableNameFromKeyFieldName(InsertedTable.Columns(COLUMN_ZERO).ColumnName)
'EmitDebugMessage("InsertedTable.TableName is: " & InsertedTable.TableName) ' DEBUG
End If
End If
Dim DeletedRow As Data.DataRow = Nothing
If DeletedTable.Rows.Count > RowIndex Then
DeletedRow = DeletedTable.Rows(RowIndex)
If TableName = "" Then
' FUTURE: Use "DeletedTable.TableName" when Microsoft fixes the CLR to supply it.
TableName = DeriveTableNameFromKeyFieldName(DeletedTable.Columns(COLUMN_ZERO).ColumnName)
'EmitDebugMessage("DeletedTable.TableName is: " & DeletedTable.TableName) ' DEBUG
End If
End If
' get the current database user
Dim CurrentUserCmd As New SqlCommand("select current_user", Connection)
Dim CurrentUser As String = CurrentUserCmd.ExecuteScalar().ToString()
' Perform different audits based on the type of action.
Select Case Context.TriggerAction
Case TriggerAction.Update
' Ensure that both INSERTED and DELETED are populated. If not, this is not a valid update.
If InsertedRow IsNot Nothing And DeletedRow IsNot Nothing Then
' Walk through all the columns of the table.
For Each Column As Data.DataColumn In InsertedTable.Columns
' ATERNATIVE CODE records any attempt to update, whether the new value is different or not.
'If Context.IsUpdatedColumn(Column.Ordinal) Then
' IMPLEMENTED CODE to compare values and record only if they are different:
If Not DeletedRow.Item(Column.Ordinal).Equals(InsertedRow.Item(Column.Ordinal)) Then
' DEBUG output indicating field change
#If DEBUG Then
EmitDebugMessage("Create UPDATE Audit: Column Name = " & Column.ColumnName & _
", Old Value = '" & DeletedRow.Item(Column.Ordinal).ToString & "'" & _
", New Value = '" & InsertedRow.Item(Column.Ordinal).ToString & "'")
#End If
' Create audit record indicating field change
Dim AuditRow As Data.DataRow
AuditRow = AuditTable.NewRow()
With AuditRow
' populate fields common to all audit records
Dim RowId As Int64 = CType(InsertedRow.Item(COLUMN_ZERO), Int64)
WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, AUDIT_OPCODE_UPDATE)
' write update-specific fields
.Item("FieldName") = Column.ColumnName
.Item("OldValue") = DeletedRow.Item(Column.Ordinal).ToString
.Item("NewValue") = InsertedRow.Item(Column.Ordinal).ToString
End With
' insert the new row into the audit table
AuditTable.Rows.InsertAt(AuditRow, ROW_ZERO)
End If
Next
End If
Case TriggerAction.Insert
' If the INSERTED row is not populated, then this is not a valid insertion.
If InsertedRow IsNot Nothing Then
' DEBUG output indicating row insertion
#If DEBUG Then
EmitDebugMessage("Create INSERT Audit: Row = '" & InsertedRow.Item(COLUMN_ZERO).ToString & "'")
#End If
' Create audit record indicating field change
Dim AuditRow As Data.DataRow
AuditRow = AuditTable.NewRow()
' populate fields common to all audit records
Dim RowId As Int64 = CType(InsertedRow.Item(COLUMN_ZERO), Int64)
WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, AUDIT_OPCODE_INSERT)
' insert the new row into the audit table
AuditTable.Rows.InsertAt(AuditRow, ROW_ZERO)
End If
Case TriggerAction.Delete
' If the DELETED row is not populated, then this is not a valid deletion.
If DeletedRow IsNot Nothing Then
' DEBUG output indicating row insertion
#If DEBUG Then
EmitDebugMessage("Create DELETE Audit: Row = '" & DeletedRow.Item(COLUMN_ZERO).ToString & "'")
#End If
' Create audit record indicating field change
Dim AuditRow As Data.DataRow
AuditRow = AuditTable.NewRow()
' populate fields common to all audit records
Dim RowId As Int64 = CType(DeletedRow.Item(COLUMN_ZERO), Int64)
WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, AUDIT_OPCODE_DELETE)
' insert the new row into the audit table
AuditTable.Rows.InsertAt(AuditRow, ROW_ZERO)
End If
End Select
' update the audit table
AuditAdapter.Update(AuditTable)
Next
' finish
#If DEBUG Then
EmitDebugMessage("Exit Trigger")
#End If
Catch e As Exception
' Put exception handling code here if you want to connect this to your
' database-based error logging system. Without this Try/Catch block,
' any error in the trigger routine will stop the event that fired the trigger.
' This is early-stage development and we're not expecting any exceptions,
' so for the moment we just need to know about them if they occur.
Throw
End Try
End Sub
''' <summary>
''' Write data into the fields of an Audit table row.
''' </summary>
Private Shared Sub WriteCommonAuditData(ByVal AuditRow As DataRow, ByVal TableName As String, ByVal RowId As Int64, ByVal CurrentUser As String, ByVal Operation As String)
With AuditRow
' write common audit fields
.Item("TableName") = TableName
.Item("RowId") = RowId
.Item("OccurredAt") = Now()
.Item("PerformedBy") = CurrentUser
.Item("Operation") = Operation
End With
End Sub
End Class
|
Compile the above routine into an assembly. Now start up the SQL 2005 Management Studio and create a query window connected to your test database,. Begin by enabling CLR using the following Transact-SQL statements:
-- Configure the server for CLR operation. sp_configure 'clr enabled', 1 GO reconfigure GO |
For testing purposes, produce and populate a sample "ADDRESS" table to operate upon:
-- Create a test table.
if object_id('ADDRESS','U') is not null drop table ADDRESS
GO
create table ADDRESS (
AddressId bigint IDENTITY(1,1) NOT NULL,
AddressLine varchar(50) NULL,
City varchar(50) NULL,
StateProvince varchar(50) NULL,
PostalCode varchar(10) NULL,
CountryCode varchar(10) NULL,
constraint ADDRESS_PK primary key clustered
( AddressId asc ) with (IGNORE_DUP_KEY = off) on [PRIMARY]
) ON [PRIMARY]
GO
-- Populate the test table.
declare @Counter int
set @Counter = 0
declare @CounterString varchar(10)
while @Counter < 10
begin
set @Counter = @Counter + 1
set @CounterString = cast(@Counter as varchar(10))
declare @AddressLine varchar(50)
set @AddressLine = 'Address Line ' + @CounterString
declare @City varchar(50)
set @City = 'City ' + @CounterString
declare @StateProvince varchar(50)
set @StateProvince = 'State/Province ' + @CounterString
declare @PostalCode varchar(10)
set @PostalCode = @CounterString
declare @CountryCode varchar(10)
set @CountryCode = 'C' + @CounterString
insert into ADDRESS
(
AddressLine,
City,
StateProvince,
PostalCode,
CountryCode
)
values
(
@AddressLine,
@City,
@StateProvince,
@PostalCode,
@CountryCode
)
end
GO |
Now create the audit table that the trigger routine will populate:
-- Create the audit table.
if object_id('AUDIT','U') is not null drop table AUDIT
GO
create table AUDIT (
-- audit key
AuditId bigint identity(1,1) not null,
-- required info for all auditing operations
TableName varchar(50) not null,
RowId bigint not null,
Operation varchar(10) not null,
OccurredAt datetime not null,
PerformedBy varchar(50) not null,
-- the following fields are used only when Operation = 'UPDATE'
FieldName varchar(50) null,
OldValue varchar(1000) null,
NewValue varchar(1000) null,
constraint AUDIT_PK primary key clustered
( AuditId asc ) with (IGNORE_DUP_KEY = off) on [PRIMARY]
) on [PRIMARY]
GO |
From this point on we are listing operations that you will need to redo every time you recompile the trigger. Use this code to load the server with the "AuditCommon" assembly containing the generic trigger routine (you must substitute your own project path here):
-- Create the assembly. When dropping the old one we must drop any
-- triggers created from it first.
declare @ProjectPath varchar(1000)
set @ProjectPath = 'YOUR PROJECT PATH GOES HERE'
declare @SolutionFolder varchar (100)
set @SolutionFolder = 'AuditCommon'
declare @ProjectFolder varchar (100)
set @ProjectFolder = 'AuditCommon'
declare @AssemblyPath varchar(1000)
set @AssemblyPath = @ProjectPath + '\' + @SolutionFolder + '\' + @ProjectFolder + '\bin'
declare @AssemblyName varchar(100)
set @AssemblyName = 'AuditCommon'
declare @AssemblyObject varchar(1000)
set @AssemblyObject = @AssemblyPath + '\' + @AssemblyName + '.dll'
declare @AssemblyDebug varchar(1000)
set @AssemblyDebug = @AssemblyPath + '\' + @AssemblyName + '.pdb'
if object_id('Audit_Address','TA') is not null drop trigger Audit_ADDRESS
if exists(select name from sys.assemblies where name = @AssemblyName)
drop assembly [AuditCommon]
create assembly [AuditCommon] from @AssemblyObject with permission_set = safe
begin try
-- This adds debugging info; the file will not be present in your "release" version
-- (as opposed to your "debug" version), so we don't want to fail if it's not there.
alter assembly [AuditCommon] add file from @AssemblyDebug
end try
begin catch
end catch
GO |
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
-- Associate the generic CLR trigger with the ADDRESS table. create trigger Audit_ADDRESS on ADDRESS for insert, update, delete as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon GO |
Now you are ready to test. Perform updates, insertions, and deletions and watch the AUDIT table fill up! Here are some sample tests:
-- Test "update"
update ADDRESS set City = 'New City 4' where AddressID = 4
-- Test "insert"
insert into ADDRESS
(
AddressLine,
City,
StateProvince,
PostalCode,
CountryCode
)
values
(
'Inserted Address 1',
'Inserted City 1',
'Inserted StateProvince 1',
'10001',
'CY1'
)
-- Test "delete"
delete from address where AddressID = 8 |
TECH NOTE 1: In this example we use the SQL Management Studio to deploy the assembly, the trigger, and the test code. The built-in Visual Studio deployment and testing mechanism will NOT work with the generic trigger because that mechanism requires that you specify a particular table in the metadata of your trigger routine, and of course our purpose here is to avoid supplying such data.
TECH NOTE 2: The CLR trigger routine relies on a field naming convention to extract the target table name because the "TableName" fields (i.e. DataTable.TableName in the code) of the "inserted" and "deleted" trigger tables are not being properly populated by SQL Server with the name of the target table (ironically these pseudo-tables contain essentially all the information you could want to know about the target table except its name). The convention involves using the table name as part of the name of the first (key) field. Obviously if your database does not employ such a convention, this trigger routine will be unable to provide the table name in your audit table. We have been informed by Microsoft that in a future release, the table name may be provided somewhere within the trigger context, but this capability will not be present in the initial release of SQL Server 2005.
NOTICE: The original version of this article was first published at SqlJunkies.com in October of 2005.
