How To Create a Database Level Audit (Trigger)?

Someone asked about Auditing yesterday and I thought I will share some info regrading the same.

You can do Field, Record and Database Audits in PeopleSoft. Record Level Audit gives the load on the App Server and can impact the performance. So the DB level Audit is something that is recommended always.

You already know the basic procedure for Field and Record level auditing - To create an audit record by copying the main record and deleting all the non-key fields and adding the 3 Audit fields and making them the key fields.

Here is how you can create a database level audit or trigger.

First you create the audit record for the main record. That step remains the same. Once you have this, go to PeopleTools > Utilities > Audits > Update Database Level Auditing

Go into the page and provide the details as below and click on the Generate Code.

Either you can copy the code and run it in the SQL Developer or use the below process to run it, if you do no have the database access.

Run the process and once it is gone to success, navigate to the log files and track the trgcode1.sql

Run the SQL in the database and you will get the below error. Solution below:

Navigate to the PSHOME Scripts folder and find out the getpsporid.sql and run it in the DB.

Then run the previous trgcode.sql script again. The trigger will be created.

Use the below SQL to search and track the trigger in the DB

And DONE!

Response on: AUDIT_RECNAME

Adding AUDIT_RECNAME is not complicated. It is an extra key field in addition to the 3 AUDIT fields. The Screenshot below shows how AUDIT_RECNAME tracks the data.