Maintaining a Log of Database Changes - Part 2By Scott Mitchell
In Part 1 of this two-part article series we looked at the motivation behind maintaining a log of database changes and compared and contrasted different techniques for persisting historical data to "history" tables. In addition to creating the table (or tables) to store the historical data, we also need to devise some mechanism to insert any changes to the "history" table(s) whenever data is inserted, updated, or deleted from a table whose history is being tracked.
Triggers are designed to execute T-SQL in response to
DELETE commands, and therefore are often used to add the historical
data to the "history" tables. Alternatively, historical information can be manually recorded each time an insert, update, or
delete is performed. This is most easily accomplished when using stored procedures to perform all data modifications.
In this article we will discuss the pros and cons of using triggers and manual techniques for maintaining changes to a database table. We will also build trigger- and manual-based implementation from the ground up. Read on to learn more!
|Your Feedback is Wanted!|
|Do you have first-hand experience in maintaining a log of database changes? If so, I would be interested in hearing your suggestions, comments, or anecdotes. Please don't hesitate to send in your feedback to mitchell@4GuysFromRolla.com.|
An Overview of Triggers
Modern database systems like Microsoft SQL Server include support for triggers, which are a construct that executes T-SQL script before or after a database table has data inserted, updated, or deleted. For example, imagine that we had a database table named
Employees with information about our company's workforce. Whenever an employee was deleted from
this table we might want to automatically add the employee's information to a separate table called
One way to accomplish this would be to create a trigger that executes after records are deleted from
This trigger would run an
INSERT statement to add the just-deleted employee records to the
While triggers provide a great deal of power and flexibility, they can very easily used in a manner that is
detrimental to the database's maintainability and performance. It is all too easy to forget that you added a trigger or to
overlook an existing trigger that was added by someone else. If you don't recall or know of a particular trigger's existence,
certain behavior may seem buggy and be confusing. Returning to the
FiredEmployee example, if you are unaware
of the trigger on
Employees you will no doubt wonder how records are getting added to the
table. Triggers are also notorious for being inefficiently designed. While inserts, updates, and deletes can occur over sets
of data (for example,
UPDATE Employees SET Salary = Salary * 2 WHERE HireDate < '2007-01-01' doubles the
salary for every employee hired before 2007), oftentimes the logic of triggers applies on a record-by-record basis. Consequently,
triggers often include
CURSORs to enumerate the inserted, updated, or deleted records one at a time. This can
introduce a performance bottleneck. Also, having multiple triggers on a table can add to the confusion - if there are three
triggers on the
Employees table that are all configured to execute after data is deleted, when records are
deleted which trigger runs first, then second, then third?
While many database experts I have spoken to warn about trigger misuse, they have all agreed that triggers are well-designed for auditing purposes. In my projects, I have always used triggers to record changes to the "history" tables. For more on triggers, including a discussion on how to add triggers to a database table, see An Introduction to Triggers.
|Recording Changes to the "History" Table(s) Using Triggers|
|Pros||Once setup, the triggers automatically start logging changes to the "history" tables and changes are recorded whether you do your updates through standardized stored procedures or if modifications occur through ad-hoc SQL statements.|
Triggers are limited in that they cannot work with
Triggers also suffer from the same maintainability issue as the "Creating a Separate "History" Table for Each Table Being Audited" approach discussed in Part 1. Namely, each time a table's schema changes, the trigger needs to be updated to reflect this change.
Finally, triggers provide less flexibility than when recording the history manually. For example, imagine you had a website
that required users to log on before they could insert, update, or delete data through the web interface and that you use
ASP.NET 2.0's Membership system to manage users.
Assuming that all users connect to the database through the same SQL Server login, the only way to capture who made the
change is to update your original tables to include a
Implementing a Trigger-Based Audit Log Solution
The download at the end of this article includes a demo application with a database table named
Employees table captures the name, salary, hire date, and a biography of each employee at the company.
The website application that interfaces with this database includes two core pages:
CRUD.aspx- from here, authenticated users can add, update, and delete employees.
History.aspx- all visitors can view the changes made to a particular employee over time, including who made the change and when it was made.
Login.aspxpage that prompts anonymous users for their credentials. For this demo I just have a DropDownList and allow the visitor to specify who they are - Scott, Jisun, or Sam - without the need for specifying a password. The
Employeestable also includes a
ChangedBycolumn that stores the name of the user who last modified the record.
There is also an
EmployeesHistory table that holds the changes made to the records in
This implements the "Creating a Separate "History" Table for Each Table Being Audited" technique
discussed in Part 1. The
has the same schema as
Employees plus two additional columns:
datetime columns and have the default values
Employees table has two triggers, one that fires after a delete and one that fires after an insert or update.
The two triggers are similar - they insert all of the modified records into
EmployeesHistory. The only difference
is that the delete trigger inserts a value of 1 into the
EmployeesHistory.Deleted column while the inserted and
updated trigger inserts a value of 0. The T-SQL for these two triggers follows:
Note that the values of the just-deleted records are queried from the
deleted table, while the
just-inserted and just-updated records are retrieved from the
With these triggers in place, anytime a record is inserted, updated, or deleted from the
Employees table a
record will automatically be added to the
In addition to setting up the triggers, we need to have our web application use the username of the currently logged on
user when inserting, updating, or deleting data. For example, the stored procedure to add a new employee,
@ChangedBy input parameter that, in the ASP.NET page, is set to the username of the currently logged on
user. Likewise, when updating or deleting data this information must be captured. Consult
CRUD.aspx to see
how this can be accomplished when inserting, updating, and deleting data using SqlDataSource, GridView, and DetailsView controls.
Care must be taken when deleting an employee in order to properly note who made the deletion. If the user deleting an
employee is not the person that last updated the employee, then before deleting the employee it is essential that the
ChangedBy value for that employee is updated to reflect the currently logged on employee. For example,
consider what would happen if such measures weren't taken. Imagine that Scott was the last person to update employee
Tito Mitchell. If Sam then logged on and went to delete Tito Mitchell, the delete would occur and the trigger would add
a new record to
EmployeesHistory with the
Deleted columns set to 1. However, the
column would still show Scott, making it look like Scott was the one who deleted Tito! This problem is circumvented by
ChangedBy column value to Sam immediately before the
DELETE statement. For the
demo, this logic is encapsulated in a stored procedure:
History.aspx page simply lists the records for a particular
EmployeeID ordered by the
field in the
EmployeesHistory table. This shows the changes made to the selected employee's information.
As the figure below shows, employee Bonita Smith was initially added to the database by Scott on 4/12/2007 at 12:31 PM.
A minute later, Jisun updated her
Biography. Two minutes after than, Scott further updated her
Biography. A minute later,
Sam updated her
Biography. Shortly after that last update, Sam deleted her from the
Manually Recording Data Changes to the "History" Table(s)
While triggers provide a behind-the-scenes way, automated way to record changes to "history" table(s) their limitations make them less than ideal in certain situations. For example, if your data model includes
whose changes need to be tracked, triggers are out of the question, and using triggers to track who made the changes when all
users connect to the database through the same account brings with it an additional layer of complexity.
An alternative approach to triggers is to manually add changes to the "history" table(s). This is most easily accomplished if you have existing stored procedures that are used for all data modifications from the application. With such a setup you can update the insert, update, and delete stored procedures to insert a record into the appropriate "history" table after performing the requested data modification. For more information on creating and using stored procedures, see Writing a Stored Procedure.
|Manually Recording Data Changes to the "History" Table(s)|
Allows for a greater flexibility than the trigger-based approach. Can work with
In order to record the history, the insert, update, and delete requests must come through appropriate stored procedures.
If a user or DBA is able to run ad-hoc SQL statements through Query Analyzer or from the website, the changes caused by
these modifications will not be recorded in the "history" table(s).
Manually approaches also require more bookkeeping and maintenance. You need to make sure that each insert, update, and delete action occurs by calling an appropriate stored procedure and that those stored procedures have additional SQL statements to record the changes. If you forget to update a stored procedure to include this history tracking, you may not discover this missing functionality until the historical data (which hasn't been recorded) is needed!
Implementing a Manual-Based Audit Log Solution
The download at the end of this article also includes an example of a manual-recording audit log solution. I added a second table named
EmployeesHistory2 to hold the manually-recorded information. Once this table was added, all that remained
was to add an
INSERT statement to the end of the
DeleteEmployee stored procedures that added a row to
Maintaining a log of database changes poses two challenges: providing the infrastructure for recording the history and devising some technique that records changes to the "history" table(s). Both challenges include multiple, viable solutions. In Part 1 of this article series we looked at two different ways to capture changes: by adding multiple "history" tables (one for each table whose changes we want to track) and through a single, consolidated "history" table. In this article we addressed the second challenge and explored two techniques for recording changes to the "history" table(s). The first option is to use triggers, which automatically record any inserts, updates, or deletes to the "history" table(s); the second option is to manually track the changes.