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
Incidentally, this first post of 2013 happens to be my 175th post, phew!
ReplyDeleteHi, 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?
DeleteThanks
Yes, it is possible.
DeleteThanks. Is there a short answer as to how to do it?
DeleteContrats Shyam! By the way, been watching your stuff for a while and LOVE IT! Keep on keeping on!
ReplyDeleteThank you.
DeleteHello, 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..
ReplyDeleteYour stuff is really very good.keep posting stuff regarding Peoplesoft Tools.Will appreciate it.Thanks.
ReplyDeletethanks for the post.
ReplyDelete