Increased governmental oversight has amplified organizational attention concerning the tracking of content changes made to application database tables. Database log reading tools do not always provide the most useful means of reviewing specific changes for all organizational purposes. Fortunately, other reviewing options do exist. Simplicity defines the most thorough alternative databased approach for tracking table content change. Two main components comprise this tracking approach: applying "audit" columns to a table, and creating shadow tables for tracking every specific row data change. Ideally, the audit columns track four details, itemized as follows: 1) when a row was created; 2) who created the row; 3) when a row was last updated; and, 4) who last updated a row. Having these itemized details, either for actual investigation of security issues or for simple problem resolution, proves extraordinarily helpful.
The shadow table provides, essentially, a copy of the table under scrutiny for change monitoring. Every alteration of values upon a row of the base table creates a row insertion in the shadow table that captures the change. Every insert to the base table, every update against the base table, every deletion issued against the table generates an insert to the shadow table for every base table row impacted. The shadow table would mimic the base table with just minor alteration. The shadow table would contain an action identifier which specifies the captured performance activity, offering values as straightforward as INSERT, UPDATE, and DELETE. While all of the previously mentioned audit columns may not necessarily require duplication on the shadow table, a created date and created-by identifier which traps the capture of the shadow activity can prove indispensable. Additionally, this shadow table must vary from the base table by having a surrogate key, since a given row from the base table may appear multiple times within the shadow table as a result of any possible change activity after initial insertion.
The uncomplicated tactic for the population of these tracking structures becomes the database itself. Database triggers can help maintain this data simply by facilitating the capture of all changes, regardless of whether the data-change source comes from an application or ad hoc query. The execution of an INSERT, UPDATE or DELETE will invoke each trigger. By consistently applying triggers and using them solely for capturing and maintaining data, then one may avoid at least some of the controversy and usual complaints about triggers as "hidden" application code. The triggers serve as the "auditing police" that help ensure data change knowledge retention. Consistency remains crucial for having an easily maintained data tracking policy. Based on a particular application design (depending on the expected functionality), some could argue that a specific monitored table may require only a created date, but not a last-updated date. Choosing this paired down perspective at a table-by-table level means that every subsequent table or process change (from an expected insert-only mode to an updated-sometimes mode) will drive the evaluation of table changes to add extra columns previously avoided. As an alternative, simply having all four items available all the time, regardless of initial expectations, prevents needless worry regarding a simple change becoming a bigger change.
Certainly, having shadow tables for every single table across every application could seem excessive in storage, coding and processing cycles. Perhaps not every table requires such complete change knowledge. An alternative option might have two levels of tracking support, i.e., always using the audit columns, but sometimes using the shadow table. While not every situation may require every option every time, determining how best to audit change means one must choose between the power and ease of consistency or endure creating the possibility of a snare of varying implementations.