1/1/13

How to combine PeopleSoft Audit and Database Trigger?

PeopleSoft's delivered Audit feature works only for changes made from online. We depend on RDBMS's trigger feature to track changes made through non-PeopleSoft methods. PeopleSoft has come up with a Database Level auditing feature using which we can combine both the PS delivered Audit and Database's Trigger feature.

Part A - Update Database Level Auditing:
Navigation: PeopleTools > Utilities > Audit > Update Database Level Auditing

1) Search for the PeopleSoft record name to audit. The system by default will create audit trigger record with the base record name suffixed by _TR
2) Select the Audit record name. Please refer to my earlier post on PS Audit for more details


3) Audit Options available are Add, Change or Delete
4) Click on "Generate Code". This will create the SQL statement to use for the Trigger creation
5) "Create Trigger Statement" section should have the SQL statement generated by above action

Note:
1) All the information entered above, gets captured in the PeopleTools trigger definition table – PSTRIGGERDEFN.
2) EnableDBMonitoring domain parameter must be set in PSADMIN for this feature to work

Part B - Perform Database Level Audit:
Navigation: PeopleTools > Utilities > Audit > Update Database Level Auditing

1) Select or create a Run Control ID
2) Select either "Create All Triggers" or "Create Trigger(s) On" depending on the requirement
3) Click Run which will trigger the Audit Application Engine - TRGRAUDPROG(Auditing Triggers). This process will fetch every row in PSTRIGGERDEFN and writes Create Trigger statements to the file - TRGCODEX.SQL, where Z represents a digit determined by the number of files that already exist in the output directory.
4) This step only creates the SQL Statement, and it should be run using SQL tool to create trigger(s)


To Delete Audit Trigger:
(Excerpt from Peoplebook)
1) Select PeopleTools, Utilities, Audit, Update Database Level Auditing
2) Open the trigger that you want to delete
3) Clear all the Audit options (Add, Change, and Delete)
4) Click Generate Code
5) Click Save
6) Drop the trigger name from the database

9 comments:

  1. Incidentally, this first post of 2013 happens to be my 175th post, phew!

    ReplyDelete
    Replies
    1. Hi, is it possible within HRMS 9.1, Tools 8.50 to audit 'Select only' statements? For example, if a user views the PERSONAL_DATA2 page, can it be captured that they were viewing an employee address?

      Thanks

      Delete
    2. Thanks. Is there a short answer as to how to do it?

      Delete
  2. Contrats Shyam! By the way, been watching your stuff for a while and LOVE IT! Keep on keeping on!

    ReplyDelete
  3. Hello, it is possible to audit tree delivered records (PSTREEDEFN, PSTREENODE, PSTREELEAF, ETC.) at record or field level? Cause I have not been able to do it... Is this way you suggest the only one to do it? Thank you very much in advance... Please help.. this is urgent for me..

    ReplyDelete
  4. Your stuff is really very good.keep posting stuff regarding Peoplesoft Tools.Will appreciate it.Thanks.

    ReplyDelete
  5. thanks for the post.

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month