Field Level & Record Level Auditing

Auditing is to perform to track the changes whatever done in the record. Its of two types:

            * Field level auditing

            * Record level auditing

Field level auditing:

The auditing mechanism enforced on a Field is known as Field level auditing. Field level auditing captures changes in one or more fields which are then stored in PSAUDIT record.  One thing to note is that only online changes are captured while Database level updates / inserts, deletes wont come under audit.

Setting up Steps:

1. open the record containing the chosen field
2. go to the Record field properties of the field(s).
3. In the Audit group box, Field Add, Field  Delete, Field  Change are the available options
4. Check the boxes to chose appropriate level of auditing
5. click OK.

Now you can perform some online transactions that affect the Record Field in question and check if auditing is working as desired; remember only the audit options you have chosen in the record field properties will be captured.

Sample query: 

Lets consider we are going to audit the EMPLID field in PS_JOB record.

SELECT * FROM PSAUDIT WHERE RECNAME = ‘PS_JOB’ AND FIELDNAME=’EMPLID’

Record level auditing

As the name suggests, record level auditing is the auditing mechanism enforced on a record.

Setting up Steps:

1. Choose the Record
2. Create the Audit Record  -  The Audit record is essentially a copy of the record being audited. The easiest way to create a copy of your record is to do a Save As. Name the new record under standard record naming convention prefixed with AUDIT_
3. Remove the unwanted fields
4. An Audit record doesn’t need keys, so remove them
5. Add Audit Fields  Insert the below fields (also known as audit fields) at the top of the new record in the below order

AUDIT_OPRID (Captures the User who performed an action)
AUDIT_STAMP (Captures the date and time when the action was performed)
AUDIT_ACTN (Captures the type of action performed)
AUDIT_RECNAME (Captures the name of the audited record definition. This field is used only when the same audit record is used for auditing multiple records)

1. On the Record Field Properties for AUDIT_STAMP, we need to check the Auto-Update checkbox. This is essential to correctly populate the date-time stamp.
2. AUDIT_ACTN can take up any of the below values based on the type of the action performed.

A – Row inserted
D – Row deleted.
C – Row changed (updated), but no key fields changed.
K – Row changed (updated), and at least one key field changed.
N – Row changed (updated), and at least one key field changed.

In case of C and K the system writes old values to the audit table. While for N, the new values are written into the audit table.

1. Build the audit record
2. Open the Base Record that is to be audited. Open its properties. On the Use tab, under the Record Audit, specify the name of the audit record that we just created as the Record Name. Choose Audit Options based on your requirement

Add – An Audit table row is inserted when a row is inserted in the base record
Delete – An Audit table row is inserted when a row is deleted from the base record
Change – Audit table row(s) is inserted when a row changes in the base record
Selective – Audit table row(s) is inserted when common field (present in both base record and audit record) changes

10.  Based on the Audit option that you have chosen, perform some transaction that can trigger the audit. Now query the audit record and check for new rows.

Record Level Audit vs Field Level Audit:

• When you realize that you need to employ auditing in a certain situation, you have got to decide between Record level audit and field level audit.
• Record level auditing provides the option to chose from the delivered PSAUDIT record or a custom built audit record. But this is not the case with Field level auditing; here PSAUDIT is your only option.
• When you realize that you need to employ auditing in a certain situation, you have got to decide between Record level audit and field level audit.
• Based on the factors outlines above, it would be wise to choose Record level auditing when the situation demands the auditing of a number of fields or a record as a whole. However, you always have another option in Field level audit when you are chasing just a filed or two.