Using Triggers to track database action history

The table we'll examine was called Account_Info and contained the following structure:

Notice the modifier column in the table. The modifier column records who added the row to the Account_Info table, and is populated by the Web application, which gets its value from the Session variable Request.ServerVariables("LOGON_USER"), which, when using NT Challenge/Response, holds the name of the logged on user.

Whenever an Insert or Update is performed on the table, the modifier is tacked on to remember who made the transaction. (Deletes are not considered in this example since most records are not allowed for deletion in most State and Federal systems; due to historical purposes, they are merely marked Inactive.) If you like, go ahead and created a couple of ASP pages that will Insert and Update the table.

While this technique will accurately record who inserted a record, or last updated a record, it still leaves a lot to be desired. For example, if a particular row is altered (updated) numerous times, the row will only contain one value in the modifier column - who performed the last update. We may want to know the complete history of changes. Also, we may want to have extra information, such as when the record was initially created, and the date/time of each update to the row.

In order to overcome these limitations, we'll create a new table whose sole purpose will be to track the history for the Account_Info table. (Note that for each table in your database design, you will need to add a new history table.) The history table, History_Account_Info, has the following database structure:

Note that for each column in the Account_Info table there is a corresponding column in the History_Account_Info table. Additionally, the History_Account_Info table contains modifier_date and action columns, to indicate when the change was made and what change (and update or insert) was made.