Creating User-Defined Audit Record Definitions

To audit at the record level, you must create a record definition and SQL table in which you store audit information. When you create a new audit record definition, name it with an AUDIT_ prefix. Some processes, such as the Employee ID Change and Employee ID Delete processes in the PeopleSoft Human Resources Management product line, change fields, such as EMPLID. These processes do not affect record definitions that begin with the AUDIT_ prefix, leaving your audit data secure.

The easiest way to create an audit table is to open the record definition that you want to audit and save it as a new record definition, prefaced with AUDIT_. Audit record definitions cannot contain key fields. Therefore, if you clone a record definition to create an audit record definition, you must remove all key attributes.

You should also use the audit-specific fields that are already defined for the PeopleTools audit tables (PSAUDIT and PSAUDITEXT), which the PeopleSoft application uses to track field-level audits. Place these audit fields at the top of the audit record definition, as you would keys. If you are creating several user-defined audit record definitions, you might consider creating and using a subrecord definition with these audit fields instead of adding them individually to each audit record definition.

This table lists audit field names and their purpose:

Audit Field Name	Purpose
AUDIT_OPRID	Identifies the user who caused the system to trigger the audits, either by performing an add, change, or delete to an audited field.
AUDIT_STAMP	Identifies the date and time the audit was triggered. Note: If there is a possibility that multiple audit rows would be generated within the same second or millisecond, then AUDIT_STAMP must be set as a duplicate key.
AUDIT_ACTN	Indicates the type of action that the system audited. Possible actions include: A: Row inserted. D: Row deleted. C: Row changed (updated), but no key fields changed. The system writes existing values to the audit table. K: Row changed (updated), and at least one key field changed. The system writes existing values to the audit table. N: Row changed (updated), and at least one key field changed. The system writes new values to the audit table.
AUDIT_RECNAME	Identifies the name of the record definition that was audited.

In most cases, you should include AUDIT_OPRID, AUDIT_STAMP, and AUDIT_ACTN. The AUDIT_STAMP must be given the attribute AUTOUPDATE. You might also add AUDIT_RECNAME if you are creating an audit table to audit more than one record definition.

Note:

Select the Auto-Update check box in the Record Field Properties dialog box; otherwise, the audit record does not receive a date and time stamp.