Click here to Skip to main content
13,354,132 members (57,915 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 7 Apr 2005

CodeSmith generated Audit Log Triggers

, 7 Apr 2005
Rate this:
Please Sign up or sign in to vote.
CodeSmith template for creating Audit Log Triggers on SQL tables.


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.


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 
-- Detail Table looks like this
-- CREATE TABLE [dbo].[<%= AuditFieldTableName %>] (
--    [FieldName] [varchar] (50) NOT NULL ,
--    [ChangeLogID] [int] NOT NULL ,
--    [BeforeValue] [sql_variant]  NOT NULL ,
--    [AfterValue] [sql_variant] NOT NULL 

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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
South Africa South Africa
I studied IT in 1987 and have been working ever since in on projects that range from coding animation in pascal and assembler to writing secure internet banking web sites.

I live in South Africa and I think because of it's size we're required to do more for less which makes highly skilled in a wide range of skills and technologies.

I have the bonus of living in a coastal town so time away from computers is spent around the sea and in sunshine and nature.

You may also be interested in...

Comments and Discussions

GeneralCannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables. Pin
icnocop18-Dec-05 14:22
membericnocop18-Dec-05 14:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 8 Apr 2005
Article Copyright 2005 by RichardRoe
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid