OmniAudit: Capture Fields

If a field is marked for "audit", whenever the content of that field changes its before and after values are written to the audit trail. When a field is marked for "capture", the captured field's current value is added to the audit trail whenever any of the audited fields in that table change. Capture values are added in addition to the normal audited changes.

Example: Suppose you are tracking customers and their orders in a database. The Orders table contains an OrderID field, a PaymentMethod field, and a CustomerID field as well as other fields containing information about the order.

The OrderID field is the primary key of the Orders table and it is how you uniquely identify individual rows in the Orders table.
The CustomerID field contains a pointer to the corresponding row in the Customers table.
The PaymentMethod field is marked for audit.
The CustomerID field is marked for capture.

If the value of the PaymentMethod field gets changed from "CK" to "CC", then the audit trail would look like this:

AuditLogID	Table	Field	Status	NewValue	OldValue
713352	Orders	PaymentMethod	Audited	CC	CK
713352	Orders	CustomerID	Captured	14223	<null>

If any of the order data was changed, you would also have a reference to the affected customer right in the audit log. As you can see, the order data may change, but the customer ID does not necessarily change. So you can't simply select the customer ID for auditing and expect to see its value in the audit log. Marking columns for capture guarantees you will always have that reference.

Lookup Fields

Lookup fields are essentially the same as captured fields, except that the reference data written to the audit trail comes from a table other than the one being audited. You define a relationship between the two tables (or use an existing foreign key) and define the value to return from the lookup table. The lookup value is then posted in the audit trail whenever an audited column has changed.

Example: Extending the example above, suppose the Customers table contains a field called AccountRepID, which contains a identifier for that customer's account representative. Instead of capturing the CustomerID in the audit trail, let's say you want to capture the AccountRepID for the matching customer instead.

In the audit setup for the Orders table, you would define how a single row in the Orders table is matched to a single row in the Customers table. Most likely this would be something like Orders.CustomerID = Customers.CustomerID, and you would probably already have a foreign key created to enforce this relationship.

Now when something in the Orders table is changed, the audit trail looks like this:

AuditLogID	Table	Field	Status	NewValue	OldValue
713352	Orders	PaymentMethod	Audited	CC	CK
713352	Orders	AccountRep	Captured	314	<null>