1. Capture who did what (e.g. "Jim modified with invoice")
1. Capture the last updated datetime of a record as well. ("Invoice 1003 last modified by Jim at 21 Nov 2005 12:35:42.767")
2. Capture the previous data and the current data with CURRENT_TIMESTAMP.
To do auditing, we need to establish a few priciples.
Any layer can audit the data available only at that layer. For example, real user name (the user who uses the application may be different from the application's user name to connect to the database. The DB won't know the application user unless specified explicitly.
There are different techniques available for auditing.
Today, its about using triggers for database auditing.
Description:
Use either instead of or after triggers to capture the data.
Advantages:
- Easy to write.
- Useful when the database is already in place.
Disadvantages:
- Performance goes down; database needs more resources; application waits until trigger is completed.
- Blob fields are not audited; update to blob field only will not be audited.
- Complexity increases when additional triggers are placed.
- Triggers are fired automatically; application has no control on that.
Some useful links:
- http://www.sqlservercentral.com/scripts/contributions/521.asp This script creates the triggers to audit table
- http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp An article on this subject
- http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=259&messageid=234334 Comments posted on above article including from me.
No comments:
Post a Comment