What are audit tables?

How are they useful?

I came across them reading this article.

  • 2
    They're just regular tables. It's how you use them and what you put into them that make them "audit tables". Mar 16, 2012 at 19:03
  • 2
    They aren't the same. You have an audit table, which is separate from the table you are auditing. So you'd have two tables, NormalTable and AuditNormalTable. Mar 16, 2012 at 19:09
  • 1
    Matt, Shark's got your answer on that. @Shark, I was stressing the point that an audit table is not a special database construct (since I got the impression the OP may believe that). It's just another table the developer builds and uses to track certain information. Mar 16, 2012 at 19:21
  • @NickChammas Oh absolutely, I completely understood what you meant. But by the OP's comment I didn't think he did. Mar 16, 2012 at 19:33
  • If you consider the dictionary meaning of audit you'll probably come to the conclusion that "audit table" is a misnomer. An audit should be external to the database: if it is coded by the application business itself, how can it be an audit?
    – onedaywhen
    Mar 19, 2012 at 8:38

4 Answers 4


Audit Tables are used to track transactions against a particular table or tables. They allow you to see an ongoing "log" (for lack of a better word). For instance, say you have a table like this:

create table SensitiveInformation
    SensitiveNumber int not null,
    SensitiveData varchar(100) not null

There may be users and/or applications that have access to insert, update, and delete out of that table. But due to the sensitive nature of that data, you may want to have a quick and easy way to track who is doing what on that table.

So you have an Audit Table to track what is being done on that table. Typically that'll include the basic Who, What, When.

An audit table could look like this:

create table SensitiveInformationAudit
    SensitiveNumberNew int null,
    SensitiveNumberOld int null,
    SensitiveDataNew varchar(100) null,
    SensitiveDataOld varchar(100) null,
    Action varchar(50) not null,
    AuditDate datetime not null,
    LastUpdatedUser varchar(100) not null

Audit Tables are typically filled through the use of Database Triggers. In other words, when X action happens on SensitiveInformation, insert the details of it in SensitiveInformationAudit.

  • Is the Action field the UPDATE or INSERT or DELETE?
    – leeand00
    Dec 9, 2013 at 15:12

One other important aspect of audit tables that hasn't been highlighted thus far, is that in addition to keeping track of who did what to which record (often including before and after snapshots) audit tables are write-once.

Records in an audit table may not be updated or deleted (see note), only inserted. This is sometimes imposed using triggers or maybe just application logic, but it's important in practice because it gives you "proof" that nothing's been tampered with in a way which is difficult to detect.

Note: Cleaning out old records from an audit table requires special processes which often have to be approved by management or auditors.


Audit tables are generally used when you want to track changes in sensitive/confidential tables. If there is a table that is used for pay rate and bonus percentage, and the HR application remunerates salaries based on this data, then a user with write access to this table can make unauthorized payment modifications.

At the same time, some users should be allowed to work on these tables. This is where audit tables come in. Audit tables can be used to track the before and after value of the changed data. Usually they also save extra information like the person who did the change and the time at which the change was done.

Hence these audit tables deter empowered users from performing unauthorized activities. They also provide a means to revert back to the correct values.

SQL 2008 and above have a built in feature called Change Data Capture that can be used for this.

  • 3
    Sadly Change data capture is terrible for auditing because you can't capture the user who made the change (unless you are already storing that in the db).
    – HLGEM
    Mar 16, 2012 at 21:34

Audit tables are used by native or 3rd party auditing tools that capture data changes that have occurred on a database, usually including the information on who made the change, which objects were affected by it, when it was made as well as the information on the SQL login, application and host used to make the change. All captured information is stored in the auditing tables and should be available in user friendly formats via exporting or querying.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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