Published: Wednesday, April 25, 2007
Maintaining a Log of Database Changes - Part 2
By Scott Mitchell
Introduction
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
INSERT, UPDATE, and 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 FiredEmployees.
One way to accomplish this would be to create a trigger that executes after records are deleted from Employees.
This trigger would run an INSERT statement to add the just-deleted employee records to the FiredEmployees
table.
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 FiredEmployees
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.
|
| Cons |
Triggers are limited in that they cannot work with text or image columns, which are
used to store large amounts of text or binary data. The good news is that in Microsoft SQL Server 2005 these limitations
can be circumvented by using the data types varchar(MAX) (or nvarchar(MAX)) and
varbinary(MAX) instead of text and image, respectively.
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 ChangedBy column and to assign that value to the currently
logged on user when inserting, updating, or deleting. The demo examined in the "Implementing a Trigger-Based Audit Log Solution"
section looks at this challenge in more detail. (If each user connects to the database through a unique SQL Server login
then you can use the SUSER_SNAME() function
to return the name of the currently logged on user.)
|
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.
The 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.
There's also a
Login.aspx page 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
Employees table also includes a
ChangedBy column 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 Employees.
This implements the "Creating a Separate "History" Table for Each Table Being Audited" technique
discussed in Part 1. The EmployeesHistory table
has the same schema as Employees plus two additional columns: Deleted and ChangedOn, which
are bit and datetime columns and have the default values 0 and
getdate(), respectively.
The 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:
-- The delete trigger, which fires after a DELETE statement has been executed
CREATE TRIGGER td_EmployeesAudit ON dbo.Employees
FOR DELETE
AS
INSERT INTO EmployeesHistory(EmployeeID, FirstName, LastName, Salary, HireDate, Biography, ChangedBy, Deleted)
SELECT EmployeeID, FirstName, LastName, Salary, HireDate, Biography, ChangedBy, 1
FROM deleted
-- The insert/update trigger, which fires after an INSERT or UPDATE statement has been executed
CREATE TRIGGER tiu_EmployeesAudit ON dbo.Employees
FOR INSERT, UPDATE
AS
INSERT INTO EmployeesHistory(EmployeeID, FirstName, LastName, Salary, HireDate, Biography, ChangedBy, Deleted)
SELECT EmployeeID, FirstName, LastName, Salary, HireDate, Biography, ChangedBy, 0
FROM inserted
|
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 inserted table.
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 EmployeesHistory table.
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, AddEmployee,
accepts a @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 ChangedBy
column would still show Scott, making it look like Scott was the one who deleted Tito! This problem is circumvented by
updating Tito's ChangedBy column value to Sam immediately before the DELETE statement. For the
demo, this logic is encapsulated in a stored procedure:
CREATE PROCEDURE dbo.DeleteEmployee
(
@EmployeeID int,
@ChangedBy nvarchar(50)
)
AS
-- TODO: Wrap this in a transaction; see http://www.4guysfromrolla.com/webtech/080305-1.shtml
-- First, update the ChangedBy field on Employees if needed
UPDATE Employees SET
ChangedBy = @ChangedBy
WHERE EmployeeID = @EmployeeID AND ChangedBy <> @ChangedBy
-- Finally, delete the record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID
|
The History.aspx page simply lists the records for a particular EmployeeID ordered by the CreatedOn
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 HireDate and Biography. Shortly after that last update, Sam deleted her from the
database.
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 text or image columns
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) |
| Pros |
Allows for a greater flexibility than the trigger-based approach. Can work with text and image
columns.
|
| Cons |
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 AddEmployee, UpdateEmployee, and
DeleteEmployee stored procedures that added a row to EmployeesHistory2.
Conclusion
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.
Happy Programming!
By Scott Mitchell
Read Maintaining a Log of Database Changes - Part 1
Attachments:
Download the demos referenced in this article
