Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
Thanks
Every table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more.
|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>