Click here to Skip to main content
12,692,580 members (33,766 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked

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

Questioni need help Pin
OM_abedo11-Apr-12 2:17
memberOM_abedo11-Apr-12 2:17 
GeneralCannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables. Pin
icnocop18-Dec-05 14:22
membericnocop18-Dec-05 14:22 
GeneralNice Idea, but it doesn't work... Pin
Captain Scarlet8-Apr-05 0:26
memberCaptain Scarlet8-Apr-05 0:26 
I'm all for use of Code smith (and I'm building an auditing system just now), so this was of great interest. I ran the template against Northwind as a trial (fine) and created the tables and triggers. When creating the FieldValues Table I got the follwoing warning:

Warning: The table 'FieldChange' has been created but its maximum row size (16113) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

when I try and change a value in the audited table (Orders) like so:

update orders set shipvia =1 where orderid = 10248

All I get is

Server: Msg 8152, Level 16, State 2, Procedure AuditUpd_Orders, Line 63
String or binary data would be truncated.
The statement has been terminated.

You may need to make the beforevalue and aftervalue columns into binary to get this working
(I've not had time to try it yet).



Captain Scarlet

Captain Scarlet is indestructible, you are not.
Remember this, do not try to copy him...
GeneralRe: Nice Idea, but it doesn't work... Pin
cosmicb14-Apr-05 2:24
membercosmicb14-Apr-05 2:24 
GeneralRe: Nice Idea, but it doesn't work... Pin
Captain Scarlet18-Apr-05 0:10
memberCaptain Scarlet18-Apr-05 0:10 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170118.1 | Last Updated 8 Apr 2005
Article Copyright 2005 by RichardRoe
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid