Audit Table - Trigger

Question

Audit Table - Trigger

Kayal-717623

Ten Centuries

Points: 1390
More actions
April 18, 2008 at 2:59 pm

#380071

I need to track all changes to my database at the application level. Say if anybody logs into the application with his user id and password, any add,delete,update should be tracked. This is an application with 5000 transactions a day. I thought of using a trigger on all the tables to capture the following fields.
[UserID]
[AuditDate]
[TableName]
[PrimaryKey]
[Action]
[PreviousValue]
[CurrentValue]
I thought of adding all the application user as a database user. So then userID will be SYSTEM_USER , and AuditDate will be GETDATE(). Is this a good idea?
I dono whether the field primarykey is really essential in my audit table? Please advice.
The field 'Action' will be update/delete/insert. Previous and Current value will be the entire record from the temp tables inserted and deleted in triggers. I dono whether this is a good idea to store the entire record or just the field which was changed or updated(as updates r gonna be more than insert and delete), and the entire record for insert and delete. Is that possible? We have columns_updated() in sql server 2005 where we can we find the columns which was updated. Will that work?
Can i capture all actions(inser/update/delete) in a trigger with FOR INSERT,UPDATE,DELETE ? If so, how can i update "ACTION" and implement something different for update(as discussed earlier) . Is there any other field which you think i should capture which might be important as a part of auditing or tracking.
Can we use trigger for all tables in the database at one time?
I know there are too many questions:) . I wud greatly appreciate any thoughts or help for any portion of it.
Thank You very much for your time,
Kayal

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

First, there are questions to be asked about the purpose of the auditing. How will it be used?

If it's meant to provide legally defensible audit trails, which might end up in court, then this isn't the route to go. There are ways to achieve that, but triggers inserting into separate tables is too easy to fake.

If it's meant to generate "undo" commands, then you'll need to store the transaction ID in the audit data, so that you can undo the whole thing. Otherwise, you end up with one row per update, and if you have multiple updates very close in time to each other, it can be confusing as to which one is which.

If it's meant for reporting and tracking purposes, then it's better to store the whole row with before and after data. I like to use XML for this, since I don't have to duplicate the column definitions between the log table and the real table.

There are products that can set up the logging for you. For example, ApexSQL Audit. Comes with several good reporting tools as well as code to set up trigger-based audit logs.

Another option to keep in mind is "passive logging". Basically, set the database in Full Recovery mode, let the log file grow, and use a log parsing program to get your audit data. RedGate, ApexSQL, and Lumigent all offer log parsing programs. This has the advantage that it doesn't add any overhead to your database, where triggers do. Also requires no maintenance on your part, which triggers will require. If you can afford to buy one of these (/convince your employer to buy one), it will be advantageous to you.

(Actually, I know that Lumigent and Apex log parsers can read from backups of log files as well as from active log files. Pretty safe bet RedGate's does too, but I haven't had a chance to try that yet. That means you don't really have to let the log file grow. But you do need Full Recovery mode, which is a good idea on production databases anyway.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 2

GSquared,

U really made me think. Yep i do agree with you on storing the entire record in XML is a good idea. I donot understand the "undo" commands. The transaction is committed and only then the triggers are triggerd. Where does the "undo" come into picture? How do you say its too easy to fake inserting into seperate tanbles using triggers?

THanks for letting me know about the products. I will defenitely have a look at them and let my employer. I am pretty sure he is not going to agree ;). If he doesnot agree do u think of any other way other than triggers.

Thanks a lot. That was really very nice of you to help us in our SQL needs. You are awesome.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

mailsar (4/21/2008)
Yep i do agree with you on storing the entire record in XML is a good idea

Gooed! Hope you don't mind your audit table being 16 times the size of a normal audit table. Works great in theory, but can you afford it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 4

What I mean by undo commands, is that you can build a proc that will take the data out of the log table and put it back into the main table. Not something in the trigger, something that can be done later based on audit history of the data.

If you go with the XML solution, like Jeff says, watch out for it to expand like crazy. The history tables (which should be in a separate database set on simple recovery mode), will quickly be bigger than the main database. It has to be managed to handle that. Depending on the transaction volume, this can be a huge problem.

Are you looking at dozens of updates per hour, or hundreds/thousands/millions? That will make a big difference in your best logging options.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

mailsar (4/21/2008)
GSquared,
U really made me think. Yep i do agree with you on storing the entire record in XML is a good idea.

Think a bit more... if you're going to store the "entire record", why not just make a copy of the table schema instead of dealing with the overhead of XML?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TriggerMe SSChampion Points: 11752 More actions · Answer 6

Jeff,

I have stumbled on what u mean bu taking a copy of the Table Schema as oppossed to using the overhead of XML as a before data & after data snapshot.

Can u please educate me on how to take a copy of the Table Schema as at the moment I'm intepreting this as the same as using Audit Tables with Triggers.

Kindest Regards,

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 7

Hi all,

There is a small change in the requirement. And so, we assume to hav only 200 transactions per day.

Thanks,

Kayal

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 8

Jeff,

Even i dint get what u mean by "copy of table schema". How do you achieve that. And how do you store the previous and current value in XML. Is it having that field as XML and then insert values manually like

cast(' ' as xml)

Thanks,

Kayal

GSquared SSC Guru Points: 260824 More actions · Answer 9

Copying the table schema is simply having two copies of the table. One copy is the live table, one is the log table. The log table usually has a couple of extra columns, like "LogDate" and "Action" and "Who". Indexing, constraints, etc., are usually different. It's just the columns that are the same.

Then, to log, you just have a trigger that inserts the "inserted" table, from the trigger, into the log table, after each transaction.

That method works. Lots of people use it.

It has the disadvantage that any change in the primary table has to be reviewed against the log table. For example, if you add another column to the primary table, you have to add it to the log table, and to the log trigger. If you change a column definition (add more characters to a varchar column, for example), you have to review what affect that will have on the log table and the log trigger.

If you don't want that maintenance overhead, inserting into a single XML column can be easier. In that case, it's a matter of using "for XML" in the select statement in the trigger.

This has the advantage that changes in the main table won't have to be reflected in the log table or the trigger. It has the disadvantage that it takes more work to query.

create trigger Table_Log on dbo.Table

after insert, update

as

insert into LogDatabase.dbo.Table (LogColumn)

select *

from inserted

for XML

Something like that (with the "for xml" wrapped up with the options it needs), will insert the changed data into the log table all in one column. If you add the "deleted" table and "after insert,update,delete", it can also log the deletions and can include before-and-after data.

Again, the main thing to watch out for on this is that it will make your transactions take a tiny bit longer, and it will create a log database that will grow like crazy. You have to plan to manage that. It can be a major problem if you're already running on limited disk space. You have to plan on backups, you have to make decisions about disk usage and RAID types. And so on.

If having the log data available that way is important enough to create such a solution, it does have some very strong advantages, if done correctly.

Log only the tables that you need to run audit reports on. Less tables = less log growth. Log only the transactions you need to log, based on what you're using the log for. Keep in mind that the database log is the master log, and this is just something to be used for "undo" procs, and for management reports.

I've used this kind of solution for things like management analysis of order status and workflow. Doubled the amount of production we could do, with very little cost.

Like all things, it has good and bad effects.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

Good explanations, Gus.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 11

Hi GSquared,

Thats awesome.. I did not know about SELECT .. FOR XML until now. Thanks a lot :). I appreciate all your suggestions. Thats a good one.

Thanks,

Kayal