Audit log Design : Using AFTER INSERT, UPDATE, DELETE Trigger

In my last project, we had to implement Audit log functionality for SQL Server Data Changes. Due to very strict timeline(Project was of 2 months and Out of that Audit functionality was having only 10 days to implement), we were goggling for ready made design for Audit functionality. But our efforts gone into vain. Didn't find any good.

SQL Server 2008 is having in built functionality to capture data changes in the form of Change Data Capture Tool(CDC). We tried to convince the client to use the SQL Server 2008. But they didn't agree.:(. We had to go with SQL Server 2005.

We followed trigger based approach. We created AFTER INSERT, UPDATE, DELETE triggers on the all client tables, and recorded all the changes happened. The design was very simple. Hope this helps you guys.

Features included are:

1. Audit Log SHOULD have turn off & turn on switch.
2. Audit Log MUST capture information like who made the changes, what changes, when the changes occurred.
3. Audit Log SHOULD have rollback facility.
4. Retrieval of Audit details in Paging.
5. Audit retrieval WOULD have following APIs
   • Get all changes
   • Get all today’s changes.
   • Get all changes for period.
   • Get all changes for particular transaction.
   • Get single Audit Log entry for given Id

Block diagram to show entire architecture used for Audit:

Audit Log Table:

CREATE TABLE [Administration].[AuditLog]( [AuditLogID] [bigint] IDENTITY(1,1) NOT NULL, [TransactionID] [bigint] NOT NULL DEFAULT ([dbo].[ufn_GetTransactionID]()), [ApplicationName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetApplicationName]()), [ObjectID] [int] NOT NULL, [OperationID] [tinyint] NOT NULL, [PerformedBy] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ([dbo].[ufn_GetContextLogin]()), [PerformedAt] [datetime] NOT NULL DEFAULT (getdate()), [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PrimaryKeyColumn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PrimaryKeyValue] [int] NOT NULL, [PreviousValue] [xml] NULL, [NextValue] [xml] NULL

Table Schema:

	Name	Data Type	Description
	ID	bigint	Primary key - AuditLogID
	TransactionID	bigint	Transaction ID used while performing DML query. This column is used to find out all the changes made in single transaction.
	ApplicationName	nvarchar(max)	Application though which SQL query is made.
	ObjectID	int	Table Id – To know on which table the operation happened.
	OperationID	tinyint	1 = Delete 2 = Insert 3 = Update
	PerformedBy	nvarchar(100)	To store who has performed the operation.
	PerformedAt	datetime	Time when operation occurred.
	TSQL	nvarchar(max)	To store exact query which made this AuditLog entry.
	PrimaryKeyColumn	nvarchar(max)	To store primarykey column name of the Table on which operation occurred.
	PrimaryKeyValue	int	Primary key value.
	PreviousValue	XML	PreviousValue for changed row. Note: For insert operation this value is null.
	NextValue	XML	CurrentValue for changed row. Note: For delete operation this value is null.

Stored Procedures:

1. [Administration].[usp_EnableAuditLog]

Description: Enables Audit for database activities.

CREATE PROCEDURE [Administration].[usp_EnableAuditLog] AS BEGIN -- All 3 steps are done in transaction-block. -- 1. Create IUD trigger on all ARIC tables. -- 2. Create cleanup job. -- 3. Set Audit flag to true in database. END

2. [Administration].[usp_DisableAuditLog]

Description: Disables Audit.

CREATE PROCEDURE [Administration].[usp_DisableAuditLog] AS BEGIN -- Both below steps are done in transaction-block. -- 1. Delete all IUD triggers which were used for auditing purpose from all the ARIC tables. -- 2. Delete cleanup job. -- 3. Set Audit flag to false in database. END

3. [Administration].[usp_GetAuditLog]

Description: Retrieve log depending on specified input parameters.

CREATE PROCEDURE [Administration].[usp_GetAuditLog] @TransactionID BIGINT = NULL, @TableName NVARCHAR(100)= NULL, @UserName NVARCHAR(100)= NULL, @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @PageSize INT = NULL, @PageNumber INT = NULL, @TotalPages INT OUT AS BEGIN -- This Stored procedure is used by below 4 APIs -- a. Get all changes -- b. Get all today’s changes. -- c. Get all changes for period. -- d. Get all changes for particular transaction. -- Retrieves changes entries as per input criteria specified. -- Entries are returned in pages. END

4. [Administration].[usp_GetAuditLogEntry]

Description: Retrieves single log information according to AudiLogID.

CREATE PROCEDURE [Administration].[usp_GetAuditLogEntry] @AuditLogID BIGINT AS BEGIN -- Retrieves single log information according to AudiLogID END

5. [Administration].[usp_Rollback]

Description: Revert the update change occurred.

CREATE PROCEDURE [Administration].[usp_Rollback] @AuditLogID BIGINT AS BEGIN -- Reverts the update change occurred. END

 

Other Audit Log Fields can be found out by using In built SQL Server functions  or DMVs. For example,

TransactionID:

SELECT TRANSACTION_ID FROM SYS.DM_TRAN_CURRENT_TRANSACTION;

ApplicationName:

SELECT program_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID

ObjectID:

Here ObjectID is the tableID in which trigger got fired. We calculated it as follows:

SELECT @ObjectID = parent_id FROM sys.triggers WHERE object_id = @@PROCID;

TSQL:

Here TSQL represents, the query responsible for firing the trigger.

DECLARE @ExecStr NVARCHAR(100);
DECLARE @inputbuffer TABLE  
(
          EventType NVARCHAR(MAX), 
          Parameters INT, 
          EventInfo NVARCHAR(MAX)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + LTRIM(RTRIM(STR(@@SPID))) + ')'

INSERT INTO @inputbuffer
EXEC (@ExecStr)
SET @TSQL = (SELECT EventInfo FROM @inputbuffer);

OperationID:

SELECT @DeletedCount = COUNT(1) FROM deleted
SELECT @InsertedCount = COUNT(1) FROM inserted
IF @InsertedCount= 0 AND @DeletedCount = 0 
       RETURN;
SET @OperationID = CASE WHEN @DeletedCount > @InsertedCount THEN 1 – DELETE
           WHEN @DeletedCount < @InsertedCount THEN 2 -- INSERT 
           WHEN @DeletedCount = @InsertedCount THEN 3 -- UPDATE
 END

etc…..

~ Kiran