Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My company is changing from a distributed Access Database model to using a centralized SQL Database. The datatables were designed such that all of the tables have a modified date. In discussion it was suggested that since we will be creating a trigger on each table to handle the modified date perhaps we should have the trigger also log some information to an audit table.

Is this the best way to setup auditing so that we can track who is changing information or is there a better way? Links to articles on the subject are welcome.

For the audit I'm looking at capturing the table name, column name, date modified, row id and the username of the person making the change. Is there any information I'm not thinking of that I should be capturing that might help me avoid future pit falls?

share|improve this question
1  
It is not uncommon to introduce an audit table that looks identical to the source table with additional supporting dates in both the source and audit table to capture the record as it was before an update was applied. I don't have specific links to share but I have worked with offloading data from ERP systems into a data warehouse where this practice was done on the ERP system. –  RobPaller Dec 30 '11 at 18:57
    
add comment

4 Answers 4

up vote 5 down vote accepted

Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

  • You can easily create a view across the main table and audit table to show a complete history includint current versions.
  • It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
  • The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
  • It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete). You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so. Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record. If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.

share|improve this answer
    
so if I'm understanding this correctly. I would make a copy of the table with additional fields to hold the logging information. When a change happens to the original table I copy the original contents with the extra data to the copy table and make the changes to the original table. Am I reading this right? –  Lumpy Dec 30 '11 at 19:46
    
Yes. The original table can have whatever audit logging information you want as well. If you have this on the original table it will get copied automatically, with the provisos about deletions as described in the answer. –  ConcernedOfTunbridgeWells Dec 30 '11 at 19:53
add comment

If you're using SQL Server 2008 Enterprise Edition or newer, you may also consider using Change Data Capture, which:

  • has a lower impact on the system than triggers;
  • covers the scenarios you mentioned;
  • the auditing logic is proven built in into SQL Server (instead of triggers, which you would have to implement yourself).
share|improve this answer
    
Good to know. Unfortunatly we're using SQL Server 2008 Standard Edition. I should have mentioned that in my post. Excellent post though. Thank you. –  Lumpy Jan 3 '12 at 12:41
add comment

Sadly CDC will not work as a replacement.

CDC doesn't include the WHO when captruing data changes; only the what and the when. If you don't need to know who made the change then CDC will work but if the WHO is as important as the what and when then sadly, CDC falls short; very short.

share|improve this answer
add comment

You can do this via auditing triggers which provide auditing data changes as answers to the following questions:

  1. Who changed the data?
  2. What was the date and time when a change occurred?
  3. Which client software was used to access the data?
  4. In case the request was data modification (the UPDATE statement), what was the data value prior and after the change?

It means that when a trigger is fired, all this info has to be collected from a database or SQL Server instance and stored in an auditing repository For example, a trigger fired when a record is inserted into the Currency.Sales table is created using the following SQL

CREATE TRIGGER Sales.tr_i_AUDIT_Currency
ON Sales.Currency
    FOR INSERT
    NOT FOR REPLICATION
AS

Then, the trigger must insert the computer name where INSERT was executed, the user name of the person who inserted the record and the name of the application used to insert the record. Triggers leverage built-in SQL Server functions to get these values:

  • HOST_NAME () returns the workstation name
  • APP_NAME()returns a name of the application in the current session
  • SUSER_SNAME() – returns the login name
  • GETDATE() – returns the system timestamp for the database currently connected to

These values are stored in a pre-defined table used as an auditing data repository

INSERT
INTO AdventureWorks2012.dbo.AUDIT_LOG_TRANSACTIONS
(
     TABLE_NAME,
     TABLE_SCHEMA,
     AUDIT_ACTION_ID,
     HOST_NAME,
     APP_NAME,
     MODIFIED_BY,
     MODIFIED_DATE,
     [DATABASE]
)
VALUES(
'Currency',
'Sales',
2, --   ACTION ID For INSERT
CASE
WHEN
       LEN(HOST_NAME())
       <
       1 THEN ' '
    ELSE HOST_NAME()
END,
CASE
WHEN
       LEN(APP_NAME())
       <
       1 THEN ' '
    ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
'AdventureWorks2012'
);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.