April 18, 2008 at 2:57 pm
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
April 21, 2008 at 11:46 am
It is not such simple task as to fit into one table that serves all.
1. You can't have PreviousValue, CurrentValue for all tables and fields because they may have all different data types.
2. Primary key is essential to identify the record updated, but you also need to have a field which describes which field in your target table was updated.
3. What about inserts? You will need to save entire record for it. And they all of different formats, depending on a particular table.
So I would suggest to have a audit table for each working data table. You don't need to create a audit table for lookups, descriptions, etc. You can also separate it to another schema or database audit.
April 21, 2008 at 3:27 pm
Mark,
That was a good one, i didnot think about different data types. The previous and current value means the entire record previous to update and after update. Its the same for delete and inserted. Yep, you are right, I definitely need to have primary key. That was useful. Thanks a lot for your tips. I appreciate it.
Thanks,
Kayal
April 21, 2008 at 6:41 pm
It's really a PITA when folks double post... have a little forum courtesy, please.
http://www.sqlservercentral.com/Forums/Topic487503-338-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2008 at 9:38 pm
You really want an audit table for every table you audit. Otherwise you introduce a scale issue and potential hot spot in your application.
If you don't want to change the application, use triggers, be sure that normal users cannot access the audit tables. Only insert into them.
April 22, 2008 at 7:29 am
Jeff,
When i logged on to SQL server central, there were different groups. I was confused into which group shud i post this question. I found 2 groups which i thought would be able to help medevelopment and TSQL). I donot know how this groups inside SQLcental works though. My assumption was the people belonging to a group will get the messages pertaining to that group like administration, development, TSQL. Thats the reason i posted in both the groups. I am sorry.
Kayal
April 22, 2008 at 7:30 am
Thanks, steve.. That helps
April 22, 2008 at 7:08 pm
mailsar (4/22/2008)
Jeff,When i logged on to SQL server central, there were different groups. I was confused into which group shud i post this question. I found 2 groups which i thought would be able to help medevelopment and TSQL). I donot know how this groups inside SQLcental works though. My assumption was the people belonging to a group will get the messages pertaining to that group like administration, development, TSQL. Thats the reason i posted in both the groups. I am sorry.
Kayal
Ah... ok... I get it. Thank you for the feedback.
Just so you know, you'll find that most of the people who provide answers to questions monitor multiple or all of the "groups" and take pretty sever exception to double posting. Not just true on just this forum... most forums end up that way, as well. The problem is that you may get the answer that you want on one "group" and no one knows that in the other "group".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2008 at 10:41 am
There are different ways of implementing Audit Logging. Before you start, you may Google a bit to see what tools may be available to help. You may find something that does everything you need.
Consider how you want the log information to be captured:
By Trigger:
Pros: Always captured, no application change
Cons: Performance impact, may increase potential for blocking issues.
By Application or Stored Proc:
Pros: Flexible, possibly less performance impact
Cons: No guarantee that application always logs, change in development personnel requires specific instructions and training. To me, this is a "deal breaker". If I can't trust the audit log, it is no good.
By capturing Log file entries:
Pros: Always captured, minimal performance impact
Cons: Not sure, maybe someone can chime in? You'll probably want to obtain an existing (tested and true) product for this approach - the log file is not something you want to mess with...
Also, consider whether you want to see whole rows, or just the columns that changed. I find that it is easier to look at the whole row in order to interpret the data. However, it may obscure what actually changed.
Logging to a single table - as already pointed out - can quickly become a hot spot (performance bottleneck) and cause locking issues. However, it can have (depending on your system) the marginal benefit of making it somewhat clear the order in which changes were actually made to different tables. I have seen this done successfully by logging only the columns changed, keyed off of table name, column name, primary key value (normalized as a varchar/nvarchar value unless you use the same data type for all your PKs), and original and updated value for the updated column (also normalized to a common datatype such as a varchar/nvarchar), possibly also recording its datatype), in addition to the information about the log entry (action, user, date & time, etc.).
We have chosen to implement an audit table for each original table. We use the original table name and a standard suffix to indicate audit or history. We use triggers to capture the changes, implemented as three separate triggers since there are a handful of minor differences in their code. We only capture the "after" data, except for Delete, when we capture the data that was deleted. This means that in order to see the details of what was changed, we need to have the "before" data available in a previous audit log entry. Populate your audit tables with the "baseline" when you implement them.
Note that if your triggers also update the source table (for instance with some "LastUpdated" information), you may want to eliminate double logging caused by the secondary firing of the update trigger.
Capturing SYSTEM_USER is not helpful if you application uses connection pooling (i.e. everybody connects with a single user name). In that case, you will need an alternative method of capturing the originating user. This can be provided by the application in each operation (may be a major change to your application), or if you have a centralized point where you perform the database connection you can use SET CONTEXT_INFO after EVERY connection open, and access that in your triggers to obtain the application user name.
Finally, we use an Identity column with an AuditID as a unique index (no primary key on the audit table), and we make our audit information the first columns in the audit table, followed by the data columns.
Tore Bostrup
April 23, 2008 at 12:34 pm
Hi Tore,
Thanks a lot for your help and your time. This is good piece of information. So u mean to say having audit table for each table is a better option than having a single table? hmmm, tht seems like a good idea, but isn't maintenance an issue? Other than that i dont see any disadvatage. Thanks, for letting me know about ways to capture the user id and about the AuditID column. I appreciate it.
Thanks again for your time,
Kayal
April 23, 2008 at 1:27 pm
Having an audit table for each original table makes it easier to work with the audit data. It helps avoid the problem of the audit table being a performance issue hot spot. In addition, your audit tables may grow large over time, so this also helps the performance when you actually need to work with the audit data (compared to an all in one table).
Maintenance is really not a big issue. By locating the "source data" columns at the "end" of the columns in the audit table and make sure their order and types match the source table, you can use ALTER TABLE to add any new columns in the same way you would for the source table. You can maintain the "audit" triggers by using CodeSmith or a similar tool, or if you do NOT use an IDENTITY column for the AuditID, you can simplify your trigger to do something like
INSERT INTO _Audit
SELECT
AuditID = ,
AuditUser = SYSTEM_USER,
AuditDateTime = GETDATE(),
AuditAction = ,
AuditOtherInfo = ,
S.*
FROM -- Insert & Update trigger uses inserted, Delete trigger uses deleted.
You only need to worry about SYSTEM_USER being the same for all if you use (take advantage of) connection pooling or if you have a service layer that performs all database accesses (and runs under a service account).
Tore Bostrup
April 23, 2008 at 1:32 pm
Thanks Tore. Thant really helps. Appreciate it.
-Kayal
April 23, 2008 at 1:33 pm
OOPS, I forgot I couldn't use angle brackets in the text, so some explanatory "stuff" got lost in the trigger description:
INSERT INTO [Source Table Name]_Audit
SELECT
AuditID = [You decide how to set it or if you want it],
AuditUser = SYSTEM_USER,
AuditDateTime = GETDATE(),
AuditAction = [Insert/Update/Delete],
AuditOtherInfo = [If you want to capture additional info],
S.*
FROM [inserted/updated] as S
-- Insert & Update trigger uses inserted, Delete trigger uses deleted.
Tore Bostrup
April 23, 2008 at 1:58 pm
Got it. Thanks tore 🙂
April 24, 2008 at 12:49 pm
Hi all,
I have another clarification. How will i know the trigger is executed successfuly or not(means whether it updates the audit table). If it does not,will it rollback the changes to the base table. On the otherhand does AFTER TRIGGER triggers after commit to base table?
Thanks,
Kayal
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply