How To: Build a Change Log into Your Database

How do you track all changes for a table within a database? Easy. Build a corrisponding history table that mirrors the table in question with the addition of a datetime column to the primary key and an action column to indentify the action that got this item into the history table (Insert, Update, Delete). The history table is maintained by a trigger on the master table.

Example:
Person
-------------
PersonID (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime


PersonHistory
-------------
PersonID, RecordedDateTime (PK)
FirstName
LastName
CreatedBy
CreatedDateTime
UpdatedBy
UpdatedDateTime
ActionType