Understanding audit data

Understanding audit data

When Object auditing is enabled for a particular type of object, any changes to objects of that type generate audit data in a few different tables.

TCHANGEOPERATION

A row is added to the TCHANGEOPERATION table each time a change occurs. TCHANGEOPERATION contains the following information:

CHANGE_TYPE

The type of change (update, delete, etc.).

QUERY_PARAMETERS

The JPQL query parameters (in the case of a delete).

QUERY_STRING

The JPQL query that persisted the change (in the case of a delete).

ROOT_OBJECT_GUID

The GUID of the top-level object that was modified.

ROOT_OBJECT_NAME

The name (usually the fully-qualified class name) of the top-level object that was modified.

TYPE

Whether the change was part of a bulk operation or a single change.

CHANGE_TRANSACTION_UID

Foreign key on TCHANGETRANSACTION.

TCHANGETRANSACTION

A row is added to the TCHANGETRANSACTION table each time a change occurs. It contains a timestamp indicating the date and time the change occurred.

TCHANGETRANSACTIONMETADATA

One or more rows are added to the TCHANGETRANSACTIONMETADATA table each time a change occurs. One row is added containing the GUID of the Change Sets that contains the modified object.

TDATACHANGED

For auditing purposes, the most useful information from a business perspective is in the TDATACHANGED table. Usually at least two rows are added each time a change occurs. Each row contains details about specific data that was changed during the operation. TDATACHANGED contains the following information:

CHANGE_OPERATION_UID

Foreign key on TCHANGEOPERATION.

CHANGE_TYPE

The type of change (update, delete, etc.).

FIELD_NAME

The name of the field on the class containing the changed value.

FIELD_NEW_VALUE

The value of the field after the change.

FIELD_OLD_VALUE

The value of the field before the change.

OBJECT_GUID

The GUID of the object that was modified. If the object does not have a GUID, this column contains NULL.

OBJECT_NAME

The name (usually the fully-qualified class name) of the top-level object that was modified.

For example, if a CM user modifies the brand and the display name of a product object, the following information would be stored in TDATACHANGED.

In this case, the top-level object that was changed is a ProductImpl, but the displayName field is stored in a sub-object (LocaleDependantFieldsImpl).