SQL Server Performance Forum – Threads Archive

Audit Tables

I want to use audit tables in all my databases so that it tracks all the information like which table is modified, inserted any row, changes made to the values,old value, new value, who made changes and when it is done etc…. I want to record all the actions took over on the tables.
Can any one tell me how I need to implement that and how useful is that? Thanks!
"He laughs best who laughs last"

As far I know to have to use 3rd party software. See ours sponsors.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It is likely to be an overkill and you should ask yourself if you *really* need this. You are potentially about to multiply your data volume.
However, you can achieve this via trigger. Have a look at BOL for "inserted" and "deleted". These are two special tables available to you only in triggers that contain the data before and after modification. You would also some more column that you can fill with GETDATE() as for when a modification is done and SUSER_SNAME() for who did the modification. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

http://www.devx.com/dbzone/Article/7939/1763/page/2
http://www.akadia.com/services/sqlsrv_table_auditing.html – what was referred by Frank. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Frank
I wud be interested going for triggers to audit my database.Do I need to code a trigger for each table? Does the trigger fire rows into my audit table for each change in the tables? could you tell me how these triggers work in general? Thanks!
"He laughs best who laughs last"

Check out ApexSQLAudit. I’ve used it for several years and have been very pleased with their products.

That is what I was talking about.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

if you wants to audit those things using triggers that is possible using ‘inserted’ and ‘deleted’ tables …. regards
hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

There is another tool called DBAudit

Yes, you would need to code at least one trigger for each table you wish to audit. Here’s a very basic example of such a trigger I’ve implemented on one of my tables.
CREATE TRIGGER dbo.TrackPostfolioHistorie ON dbo.Portfolio
FOR INSERT, UPDATE
AS
INSERT INTO PortfolioHistorie (ident, ka_id, depot, nominal, valuedate, u_id)
SELECT ident, ka_id, depot, nominal, valuedate, u_id FROM inserted As you can see, I’m only interested in what’s changed to a row. In my audit table, there are two more column having DEFAULTS defined of who did the change (SUSER_SNAME()) and when (GETDATE())
It’s doing a good job —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>