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.