Thursday 28 February 2013

Creating a generic audit trigger

Creating a generic audit trigger with SQL 2005 CLR

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 LARRY 2 UPDATE 2005-09-26 11:06:01.613 dbo Name George John
2 MOE 14 INSERT 2005-09-26 11:08:02.790 dbo NULL NULL NULL
3 CURLY 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 create a custom trigger for each table. Because Transact-SQL cannot inspect the metadata associated with the "inserted" and "deleted" pseudo-tables in a trigger, each trigger routine had to know the specific structure of its target table in advance. Thus despite the fact that auditing is a relatively mundane and repetitive chore, it was impossible to write a single 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:
  • use the September "Release Candidate" version of Visual Studio 2005 and SQL Server 2005 that was distributed at the 2005 Microsoft Professional Developers' Conference (this code was not tested on previous versions);
  • 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

Partial Public Class Triggers
    ' This is the original template for Trigger metadata. Note that it is table-specific (i.e. it suggests
    ' that the trigger should apply to one table only).
    ' <Microsoft.SqlServer.Server.SqlTrigger(Name:="Trigger1", Target:="Table1", Event:="FOR UPDATE")> _

    ' This is our actual declaration. Note that 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.

    ' GENERIC AUDIT TRIGGER: AuditCommon
    '
    <Microsoft.SqlServer.Server.SqlTrigger(Name:="AuditCommon", Event:="FOR UPDATE, INSERT, DELETE")> _
    Public Shared Sub AuditCommon()

        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 Context As SqlTriggerContext = SqlContext.TriggerContext()
            Dim Connection As New SqlConnection("context connection=true")
            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 where 1 = 0", Connection)
            Dim AuditTable As New Data.DataTable
            AuditAdapter.FillSchema(AuditTable, SchemaType.Source)
            Dim AuditCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(AuditAdapter)

            ' Create DataRow objects corresponding to the trigger table rows.
#If DEBUG Then
            EmitDebugMessage("Create internal representations of trigger table rows")
#End If
            Dim TableName As String = ""

            Dim InsertedRow As Data.DataRow = Nothing
            If InsertedTable.Rows.Count > 0 Then
                InsertedRow = InsertedTable.Rows(0)
                TableName = DeriveTableNameFromKeyFieldName(InsertedTable.Columns(0).ColumnName)
            End If

            Dim DeletedRow As Data.DataRow = Nothing
            If DeletedTable.Rows.Count > 0 Then
                DeletedRow = DeletedTable.Rows(0)
                TableName = DeriveTableNameFromKeyFieldName(DeletedTable.Columns(0).ColumnName)
            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

                            ' ALTERNATIVE CODE to compare values and record only if they are different:
                            ' If Not DeletedRow.Item(Column.Ordinal).Equals(InsertedRow.Item(Column.Ordinal)) Then
                            '
                            ' This code records any attempt to update, whether the new value is different or not.
                            If Context.IsUpdatedColumn(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(0), Int64)
                                    ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                                    WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "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, 0)
                            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(0).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(0), Int64)
                        ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "INSERT")

                        ' insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0)

                    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(0).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(0), Int64)
                        ' use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "DELETE")

                        ' insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0)

                    End If

            End Select

            ' update the audit table
            AuditAdapter.Update(AuditTable)

            ' 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
    ' Write data into the fields of an Audit table row that is common to all
    ' types of audit activities.
    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
    ' 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.
    Private Shared Function DeriveTableNameFromKeyFieldName(ByVal KeyFieldName As String) As String
        Return Left(KeyFieldName, Len(KeyFieldName) - 2).ToUpper() ' assumes KeyName = TableName & "Id"
    End Function
    ' Emit debug messages. This function gives us the option to turn off debugging
    ' messages by changing one value (here).
#If DEBUG Then
    Private Shared Sub EmitDebugMessage(ByVal Message As String)
        SqlContext.Pipe.Send(Message)
    End Sub
#End If
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',
    'CI1'
)

-- Test "delete"
delete from address where AddressID = 8
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.
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.

No comments:

Post a Comment