To read the article online, visit http://www.4GuysFromRolla.com/webtech/042507-1.shtml

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

  • Article Information
    Article Title: Maintaining a Log of Database Changes - Part 2
    Article Author: Scott Mitchell
    Published Date: Wednesday, April 25, 2007
    Article URL: http://www.4GuysFromRolla.com/webtech/042507-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers