Tracking Changes Using Audit Columns and Audit Tables

In the previous two sections, you learned how to store and access historical summarized data. Now, you will learn techniques for tracking various changes to your data in the table itself and outside of the source table. The level of auditing you choose will depend on what you really need to know. You can choose a level of auditing as simple as recording the date of the change or as complex as holding a complete record of the change with the option to recover if necessary. This latter option utilizes audit columns, audit tables, or both.

Auditing with Columns

Auditing with columns has the benefit of having the audit information located in the same table with the data. Table 11-1 describes some of the common audit columns that are added to tables.

Table 11-1. Various Types of Audit Columns

Audited Events	Data Types	Comments
INSERT, UPDATE, or DELETE	DATETIME	Used to track the date and time the audited action occurred. Commonly used with GETDATE() as the default value. However, it can also be set by the calling application.
INSERT. UPDATE, or DELETE	VARCHAR	Used to track the user name or application that performed the audited action.
DELETE	BIT/TINYINT	Used to mark a record as being deleted. This can be used very efficiently in indexing and filtering.

One thing you will notice from this table is that the data change is not actually logged. The most effective use of auditing columns is tracking the fact that a change has occurred, when it occurred, and who instigated the change. You can use any combination of these columns to track changes to records within the actual table. Depending on your application and the level of auditing you desire, you will find these columns should meet most of your row-level auditing needs.

Setting Up Audit Columns

1.	You need to determine the events that you wish to audit. In this example, you will learn how to add audit columns to track who made the change, when the row was created, when the row was last updated, and whether the row has been deleted in the Person.Address table in the AdventureWorks database.
2.	Now that you have the table (Person.Address) and have decided what events to track, you need to decide what columns you will add to the table. ModifiedDate already exists in the table. It will handle the date showing when the row was last updated or deleted. The CreatedDate column will track when the record was created. It will be a column of the DATETIME datatype, using GETDATE() to provide the current date as the default value. The ModifiedBy column is a VARCHAR column that will hold the username or some other means of identifying the user or application that made the modification. The IsDeleted column is a BIT datatype column that will be used to record whether a record has been deleted. The date and user will be tracked via the ModifiedDate and the ModifiedBy columns. If the record has been deleted, this column will be marked and the modified columns will tell you when and by whom.
3.	You can now execute the script shown below to alter the Person.Address table. (This code is included in the sample files as AlterTable.sql.) USE AdventureWorks GO ALTER TABLE Person.Address ADD CreatedDate DATETIME NULL DEFAULT GETDATE() ,ModifiedBy VARCHAR(50) NULL ,IsDeleted BIT DEFAULT (0)
4.	Next, if you are modifying a table with existing data, you should set the CreatedDate to a value far enough in the past to know that it was created before the auditing was in place. Execute the following code to set the CreatedDate value. UPDATE Person.Address SET CreatedDate = '1/1/1980';
5.	Now you will need to modify stored procedures and application code to populate these columns with the desired results. You could also use triggers to update these columns, but it is usually a better practice to control how the data can be modified and use the application code to update the audit columns.
6.	The last step in this process is to add a filter to all procedures and code referencing this table to not return deleted rows. The filter to use is as follows: ... WHERE IsDeleted = 0 ...

Auditing with Tables

You have seen how to use audits to show that a change has occurred. However, the only change that can be backed out easily is the DELETE event. You can reset the IsDeleted flag and the data will be available once again. You may also be able to back out the CREATE event with enough information about the activity. However, if you need to be able to fully track the state of the data before it was modified, the best option may be to use an audit table. This is an option you want to use sparingly, as it can cause a number of maintenance and performance problems. These issues arise because rows need to be copied to the audit table and modified in the source table. For this example, you will set up table-based auditing on the Sales.Special Offer table. The goal will be to track any changes to this table and be able to back the changes out after they have been committed.

Setting Up Audit Tables

1.

Open SQL Server Management Studio, and locate the Sales.SpecialOffer table in the AdventureWorks database in Object Explorer.

2.

Generate the base audit script by right-clicking the Sales.SpecialOffer table and selecting Script Table As | Create To | New Query Editor Window from the context menus. This will open a New Query window with the CREATE TABLE script ready to be edited.

3.

Edit the script using the following steps. For this example, the final edited version of the script is listed in step 4.

3.1. First, remove all of the extra script. You should delete all lines of code not contained within the CREATE statement.

3.2. Next, change the name of the table from Sales.SpecialOffer to Sales.SpecialOffer_Audit.

3.3. Now remove any constraints on the table and allow all of the columns to accept NULL values. This will make the table more like a log table. In this case, the audit table should not prevent normal operations on the table from commencing. This should also make managing the table easier.

3.4. Add any additional columns that will help you determine the type of change, the date of the change, and any other audit items you wish to track. In this example, you will add the columns listed in Table 11-2.

Table 11-2. Columns to Add to the Audit Table

Column Name	Datatype
AuditModifiedDate	DATETIME
AuditType	NVARCHAR(20)

4.

Execute the finished script shown below on the AdventureWorks database. (This code is included in the sample files as CreateAuditTable.sql.)

USE AdventureWorks; GO CREATE TABLE Sales.SpecialOffer_Audit(     SpecialOfferID INT NULL,     Description NVARCHAR(255) NULL,     DiscountPct SMALLMONEY NULL,     [Type] NVARCHAR(50) NULL,     Category NVARCHAR(50) NULL,     StartDate DATETIME NULL,     EndDate DATETIME NULL,     MinQty INT NULL,     MaxQty INT NULL,     rowguid UNIQUEIDENTIFIER NULL,     ModifiedDate DATETIME NULL,     AuditModifiedDate DATETIME NULL,     AuditType NVARCHAR(20) null ); GO

Tip

You may want to create a new schema for audit objects.

Writing Audits to the Audit Table

Database triggers and the new OUTPUT T-SQL clause are the two primary ways to move data to the audit tables in SQL Server 2005. The use of triggers to complete this operation is pretty standard. However, the new OUTPUT clause adds some interesting capabilities. The next two sections will walk you through each of these options.

Using an UPDATE Trigger to Populate the Audit Tables

1.

Create a trigger on the Sales.SpecialOffer table that writes the data's previous state to the Sales.SpecialOffer_Audit table you have created. The code below provides a sample of the syntax you can use. (This code is included in the sample files as CreateTrigger.sql.) Enter and execute this code in a New Query window in SQL Server Management Studio.

USE AdventureWorks GO CREATE TRIGGER SpecialOfferUpdateAudit ON Sales.SpecialOffer FOR UPDATE AS   INSERT INTO Sales.SpecialOffer_Audit     (SpecialOfferID     ,Description     ,DiscountPct     ,[Type]     ,Category     ,StartDate     ,EndDate     ,MinQty     ,MaxQty     ,rowguid     ,ModifiedDate     ,AuditModifiedDate     ,AuaditType)   SELECT TOP 1 d.SpecialOfferID     ,d.Description     ,d.aDiscountPct     ,d.[Type]     ,d.Category     ,d.StartDate     ,d.EndDate     ,d.MinQty     ,d.MaxQty     ,d.rowguid     ,d.ModifiedDate     ,GETDATE()     ,'UPDATE'   FROM deleted d; GO

Note

If you have made no modifications to the Sales.SpecialOffer offer table, you most likely will need to drop the trigger that already exists on the table (uSpecialOffer). You should save the script for the trigger so you can reapply it later. If you do not remove this trigger, you will get two rows of data in the Sales.SpecialOffer_Audit table whenever you update the table.

The advantage of using a trigger is that it will capture any update that occurs on the table no matter the source. This is your "complete coverage" audit option. If you are concerned about data being changed that you are not in control of, then this is a great option. However, if you have tight control on how the data is entered into the table, especially if it is done via stored procedures, there is a new option available in SQL Server 2005 for auditing changes called the OUTPUT clause.

Using the OUTPUT Clause to Populate the Audit Tables

1.

In order to use the OUTPUT clause effectively, every event you wish to audit will require work on the stored procedures and SQL statements used to UPDATE, INSERT, or DELETE data into the audited table. The OUTPUT clause gives you access to the inserted and deleted tables within these procedures and SQL statements. You are no longer required to use a trigger to access this data. The code below shows an example of using the OUTPUT clause to audit an update in the SpecialOffer table to the SpecialOffer_Audit table. (This code is included in the sample files as UsingOutputClause.sql.) Enter and execute the following code in a New Query window in SQL Server Management Studio. Important The OUTPUT clause must insert its data into a table variable, temporary table, oras in this casea permanent table. USE AdventureWorks GO UPDATE Sales.SpecialOffer SET description = 'Big Mountain Tire Sale' OUTPUT deleted.SpecialOfferID ,deleted.Description ,deleted.DiscountPct ,deleted.[Type] ,deleted.Category ,deleted.StartDate ,deleted.EndDate ,deleted.MinQty ,deleted.MaxQty ,deleted.rowguid ,deleted.ModifiedDate ,GETDATE() ,'UPDATE' INTO Sales.SpecialOffer_Audit WHERE SpecialOfferID = 10

2.

The OUTPUT clause puts the modified data within easy access during the data modification process. The deleted prefix is available during UPDATE and DELETE operations. The inserted prefix is available during UPDATE and INSERT operations. You will notice that both are not available at the same time, unlike the deleted and inserted tables used in triggers. This mutually exclusive availability will require that you handle the various operations differently in order to collect the desired data and push it to the audit table. Tip The OUTPUT clause can also be used to return the value from an IDENTITY column during an INSERT operation.

Recovering Data with Audit Tables

Now that you have two options for loading the audit table, you can start looking at what you want to use this data for. Because all of the changes to the table are stored in the audit table, you will be able to recover any data change by overwriting the current data with the change you want to keep. The audit table will keep multiple versions of the data, so most of the time this will be a manual operation. However, you can also create a maintenance stored procedure to back out the most recent change.

Using Audit Tables to Recover Changed Data

1.

Determine which record needs to be restored. You will need to identify the record that you want to replace and the data you want to replace it with.

2.

Use an UPDATE statement that will overwrite the current data with the change you need to recover to that table. In the current example, you will need to use either the rowguid or SpecialOfferID column in combination with the AuditModifiedDate as criteria for the UPDATE statement, as shown below. (This code is included in the sample files as RecoveringChangedData.sql.) Enter and execute the following code in a new query window in SQL Server Management Studio. -- You will need to replace the AuditModifiedDate in this script with the --AuditModifiedDate from your SpecialOffer_Audit table USE AdventureWorks GO UPDATE Sales.SpecialOffer SET Description = a.Description ,DiscountPct = a.DiscountPct ,Type = a.Type ,Category = a.Category ,StartDate = a.StartDate ,EndDate = a.EndDate ,MinQty = a.MinQty ,MaxQty = a.MaxQty ,rowguid = a.rowguid ,ModifiedDate = a.ModifiedDate FROM Sales.SpecialOffer_Audit a WHERE SpecialOffer.SpecialOfferID = 10 AND a.SpecialOfferID = 10 AND a.AuditModifiedDate = '2006-04-02 22:40:27.513'

If you have data you need to recover regularly, you could encapsulate the above code into a maintenance stored procedure. However you choose to implement it, you now have options for recovering data entry issues. These options are good for a limited number of rows on the same table. If you are dealing with a bulk-load issue across multiple tables, you should look at using database snapshots as mentioned in the first section of this chapter.