Steps in short :
1. Select base record to audit
2. Create audit record - App designer or through sql
3. Define audit triggers - Select PeopleTools, Utilities, Audit, Update Database Level Auditing.
4. Creating and Running the Auditing Triggers Script (AE - TRGRAUDPROG) - Select PeopleTools, Utilities, Audit, Perform Database Level Audit.
5. Run the trigger code TRGCODEX.sql output of the above process in the local sql tool to create triggers.
6. Verify if the data is populating in the Audit tables by making some changes on the base tables.
Explanation in Brief:
Database Level Auditing page :
1. Select base record to audit
2. Create audit record - App designer or through sql
3. Define audit triggers - Select PeopleTools, Utilities, Audit, Update Database Level Auditing.
4. Creating and Running the Auditing Triggers Script (AE - TRGRAUDPROG) - Select PeopleTools, Utilities, Audit, Perform Database Level Audit.
5. Run the trigger code TRGCODEX.sql output of the above process in the local sql tool to create triggers.
6. Verify if the data is populating in the Audit tables by making some changes on the base tables.
Explanation in Brief:
Database Level Auditing page :
Create All Triggers - If you select this checkbox, the Application Engine writes the Create Trigger statement to a file for every row in PSTRIGGERDEFN.
Create Triggers on - Specify the particular table that the Trigger statement should be created for.
The Database Level Auditing process initiates the process described in Signon PeopleCode.
To create and run a trigger script:
1. Select PeopleTools, Utilities, Process, Database Level Auditing.
This displays the Run Audit Triggers page.
2. Indicate the triggers that you want to be included in the script, all in PSTRIGGERDEFN or just those related to a specific table.
3. Click Run.
This process invokes an Application Engine program that writes the Create Trigger statement to a file for every row in PSTRIGGERDEFN that you selected (all or for a specific table).
The system writes the file to the location determined by the run location of the process. If run on the server the file is created in the PS_SRVRDIR directory. If run on a Windows Workstation the file is created in the directory specified by the %TEMP% environment variable.
The file name is TRGCODEX.SQL where X represents a digit determined by the number of files by the same name that already exist in the output directory.
4. After you have created the SQL script, use your native SQL utility to run the script against your database.
For more information about deleting a trigger script, see Deleting a Trigger.
** More Information **
Database Level Auditing Usage - Trigger-based auditing functionality is provided by PeopleTools Utilities as an alternative to the record-based auditing provided by Application Designer.
This type of auditing is much better than record and/or field level auditing. It will audit changes made to any of the security tables via Tools or any other mechanism. Our record/field level auditing wouldn't catch a change made via a SQL tool (e.g. SQL*Plus) or COBOL, SQR, etc.
Object Name - TRIGAUDPNL
Navigation - Utilities, Use, Database Level Auditing
Database Level Auditing: Audit Triggers page
Audit Record Name - Use the Browse button to search the PSRECDEFN table. The Audit name must exist before a trigger can be created.
Trigger name - By default the system names audit triggers using the following naming convention
For example, ABSENCE_HIST_TR
Audit Options - Select from the options Add, Change or Delete.
Generate Code - Click this button when you have completed the above fields in order to generate the Trigger Statement.
Create Trigger Statement - The statement is populated when the Generate Code button is clicked. You can customize the script if you need to. It depends on your preference. One of the following sections contains RDBMS information to help you view the contents of the script.
PeopleSoft provides trigger-based auditing functionality as an alternative to the record-based auditing provided by Application Designer. Some countries require that you audit changes to certain data, while some companies take it upon themselves to audit who is making changes to sensitive data. This level of auditing is not only for maintaining the integrity of your data, but it is also a heightened security measure. PeopleSoft takes advantage of database triggers (offered by most RDBMS vendors), and when a user makes a change to a specified field you are monitoring, the changed data "triggers" the audit.
The information that the trigger records includes the user that made a change, the type of change made, when the change was made, and so on. Because the trigger records the user ID of the user modifying the base table, it is essential that you have the Enable DB Monitoring parameter set in PSADMIN.
Create Triggers on - Specify the particular table that the Trigger statement should be created for.
The Database Level Auditing process initiates the process described in Signon PeopleCode.
To create and run a trigger script:
1. Select PeopleTools, Utilities, Process, Database Level Auditing.
This displays the Run Audit Triggers page.
2. Indicate the triggers that you want to be included in the script, all in PSTRIGGERDEFN or just those related to a specific table.
3. Click Run.
This process invokes an Application Engine program that writes the Create Trigger statement to a file for every row in PSTRIGGERDEFN that you selected (all or for a specific table).
The system writes the file to the location determined by the run location of the process. If run on the server the file is created in the PS_SRVRDIR directory. If run on a Windows Workstation the file is created in the directory specified by the %TEMP% environment variable.
The file name is TRGCODEX.SQL where X represents a digit determined by the number of files by the same name that already exist in the output directory.
4. After you have created the SQL script, use your native SQL utility to run the script against your database.
For more information about deleting a trigger script, see Deleting a Trigger.
** More Information **
Database Level Auditing Usage - Trigger-based auditing functionality is provided by PeopleTools Utilities as an alternative to the record-based auditing provided by Application Designer.
This type of auditing is much better than record and/or field level auditing. It will audit changes made to any of the security tables via Tools or any other mechanism. Our record/field level auditing wouldn't catch a change made via a SQL tool (e.g. SQL*Plus) or COBOL, SQR, etc.
Object Name - TRIGAUDPNL
Navigation - Utilities, Use, Database Level Auditing
Database Level Auditing: Audit Triggers page
Audit Record Name - Use the Browse button to search the PSRECDEFN table. The Audit name must exist before a trigger can be created.
Trigger name - By default the system names audit triggers using the following naming convention
For example, ABSENCE_HIST_TR
Audit Options - Select from the options Add, Change or Delete.
Generate Code - Click this button when you have completed the above fields in order to generate the Trigger Statement.
Create Trigger Statement - The statement is populated when the Generate Code button is clicked. You can customize the script if you need to. It depends on your preference. One of the following sections contains RDBMS information to help you view the contents of the script.
PeopleSoft provides trigger-based auditing functionality as an alternative to the record-based auditing provided by Application Designer. Some countries require that you audit changes to certain data, while some companies take it upon themselves to audit who is making changes to sensitive data. This level of auditing is not only for maintaining the integrity of your data, but it is also a heightened security measure. PeopleSoft takes advantage of database triggers (offered by most RDBMS vendors), and when a user makes a change to a specified field you are monitoring, the changed data "triggers" the audit.
The information that the trigger records includes the user that made a change, the type of change made, when the change was made, and so on. Because the trigger records the user ID of the user modifying the base table, it is essential that you have the Enable DB Monitoring parameter set in PSADMIN.
