Tuesday, September 8, 2015

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.


3 comments:

  1. Very timely information since I'm creating a PSoft DB Trigger. I'm trying to use AUDIT_RECNAME in my audit file (just like PBooks says) but I get the following error: PLS-00049: bad bind variable 'OLD.AUDIT_RECNAME'. I want to use AUDIT_RECNAME since I need to audit changes to a field that is located in 2 different records. The audit/trigger works perfect if I don't include the AUDIT_RECNAME field. Any suggestions?

    ReplyDelete
  2. Have you had any luck using AUDIT_RECNAME as part of the Audit record? I would like to use this field since I have the same field name in 2 different records that I want to audit. I get an error when I create the trigger -- PLS-00049: bad bind variable 'OLD.AUDIT_RECNAME'.

    ReplyDelete
  3. Hello Anonymous :)
    Adding AUDIT_RECNAME is a straight forward process. Just add it to accompany the other 3 AUDIT fields and it works. Please see the last section of this post for the screenshot of AUDIT_RECNAME in action.

    ReplyDelete

Total Pageviews

Copyright © Tech & Management Notes