Audit Trail Trigger generation

Question

Working on Audit Trail system and decided to do it with Shadow/History table with triggers.

Followed this Audit Trail Article and trying to use CodeSmith Generator tool

I dont understand how it creates the history table and the trigger.

Is any one could understand how it works and help me on it.

I tried google to understand it. But there is no clear example

Nothing is clear with the below to me

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]

How to generate this and add trigger and how can i insert AuditFieldTableName values?

Partial answer: "*How to Generate this..?*" You take out the comment markers ('-- ') and execute the commands. I have taken out the comment markers for you. You probably need to put specific table names in also. — RBarryYoung, Oct 21 '13 at 13:44

score 0 · Answer 1 · answered Oct 21 '13 at 12:10

As we have different types of columns in multiple tables, the audit table that you have specified wouldn't really suffice the cause. I suggest the following audit table:

TABLE auditEntry (
    auditEntryId           INTEGER      PRIMARY KEY,
    operationType          VARCHAR(10)  NOT NULL,      -- For INSERT / UPDATE / DELETE
    changeTimestamp        DATETIME     NOT NULL,
    madeBy                 VARCHAR(50)  NOT NULL,
    tableName              VARCHAR(30)  not null,      -- stores the name of the table changed
    columnName             VARCHAR(30)  not null,      -- stores the name of column changed
    oldInt                 INTEGER,
    newInt                 INTEGER,
    oldVarchar             VARCHAR(100),
    newVarchar             VARCHAR(100),
    oldDate                DATETIME,
    newDate                DATETIME)

Now I think it's a cakewalk for you to write row level triggers for INSERT, UPDATE and DELETE on your tables, if you have working knowledge of writing them. Search MSDN on how to write such triggers and you will be fine.

The `sql_variant` columns in the original Detail table takes care of the datatype problem. — RBarryYoung, Oct 21 '13 at 13:37

Audit Trail Trigger generation

1 Answers1