CodeSmith generated Audit Log Triggers

• Download source - 2.96 Kb

Introduction

I don't think its necessary for me to sing the praises of CodeSmith. Go to the link and try it out if you haven't.

Background

I needed to keep an Audit Log of changes made to SQL Server tables, but I'd already written the application so I needed a quick and easy solution that didn't require a whole new set of Unit and System testing of my application. I knew CodeSmith had some templates that generated SQL scripts and allowed developers to create there own scripts without having to write hundreds of lines of code. The result is the AuditTriggers CodeSmith template.

Using the code

Once you've installed CodeSmith, you can run the script on your SQL Server database tables that you need Audit Logs for. It will generate a SQL script that you can install on the database or modify and install.

If you look in the comments of the generated script, you will see the Create Table script.

-- Audit Table looks like this

-- CREATE TABLE [dbo].[<%= AuditTableName %>] (

--    [ChangeLogID] [int] IDENTITY (1, 1)  ,

--    [OperationType] [varchar] (10) NOT NULL ,

--    [ChangeTimestamp] [datetime] NOT NULL ,

--    [MadeBy] [varchar] (6)  NOT NULL ,

--    [TableChanged] [varchar] (50)  NOT NULL 

-- ) ON [PRIMARY]

-- Detail Table looks like this

-- CREATE TABLE [dbo].[<%= AuditFieldTableName %>] (

--    [FieldName] [varchar] (50) NOT NULL ,

--    [ChangeLogID] [int] NOT NULL ,

--     [sql_variant]  NOT NULL ,

--    [AfterValue] [sql_variant] NOT NULL 

-- ) ON [PRIMARY]

The above script is the Log tables where all the audit logs will be inserted. Note: The AuditTrigger script doesn't generate Audit Logs for BLOB data types.

Points of Interest

For a quick and easy solution for any type of code, CodeSmith is up there with the best of them.