Click here to Skip to main content
Click here to Skip to main content
Go to top

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.

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 ,
--    [BeforeValue] [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.

License

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

Share

About the Author

RichardRoe
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.

Comments and Discussions

 
Questioni need help PinmemberOM_abedo11-Apr-12 1:17 
GeneralCannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables. Pinmembericnocop18-Dec-05 13:22 
GeneralNice Idea, but it doesn't work... PinmemberCaptain Scarlet7-Apr-05 23:26 
GeneralRe: Nice Idea, but it doesn't work... Pinmembercosmicb14-Apr-05 1:24 
GeneralRe: Nice Idea, but it doesn't work... PinmemberCaptain Scarlet17-Apr-05 23:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web02 | 2.8.140916.1 | Last Updated 8 Apr 2005
Article Copyright 2005 by RichardRoe
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid