Working With Auditing Triggers
This section discusses how to:
Define auditing triggers.
Create and run the auditing triggers script.
Delete auditing triggers.
A trigger is a database level object that the system initiates based on a specified event occurring on a table. Most RDBMS platforms support a form of database triggers.
Defining Auditing Triggers
Access the Audit Triggers page.
Image: Audit Triggers page
This example illustrates the fields and controls on the 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 by using the following naming convention <base record>_TR, for example, ABSENCE_HIST_TR.
- Audit Options
Select from the options Add, Change or Delete.
- 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.
- Generate Code
Click this button when you complete the previous fields to generate the Trigger Statement.
To define an audit trigger
Click Add a New Value.
Enter an existing base record.
On the Audit Trigger page, you need to choose the record to hold the auditing data, the audit record.
Select the events to audit, as in when data is added, changed, or deleted. You can select all of the options.
Click Generate Code.
This generates the SQL that ultimately creates the trigger.
All of this information, Record Name, Audit Record Name, Trigger Name, and Create Trigger Statement, gets saved to the PeopleSoft table, PSTRIGGERDEFN.
Perform these steps for each trigger that you want to create. After you create all the trigger statements, then you create and run the trigger script, which is described in the following section.
Important! The DDL for these database audit triggers can only be properly created using the Audit Triggers page. Do not use any other means to create the DDL for these triggers.
Creating and Running the Auditing Triggers Script
After you create and modify all of the trigger statements, you need to create and run the trigger script against the database to create the triggers.
Access the Run Audtrgs page ().
- Create All Triggers
If you select this check box, 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.
To create and run a trigger script:
Indicate the triggers that you want to be included in the script, all in PSTRIGGERDEFN or just those that are related to a specific table.
This process invokes an Application Engine program that writes the Create Trigger statement to a file for every row in PSTRIGGERDEFN that you select (all or for a specific table).
The system writes the file to the location that is determined by the run location of the process. If it's run on the server, the file is created in the PS_SRVRDIR directory. If it's run on a Windows workstation, the file is created in the directory that the %TEMP% environment variable specifies.
The file name is TRGCODEX.SQL, where X represents a digit that is determined by the number of files by the same name that already exist in the output directory.
After you create the SQL script, use the native SQL utility to run the script against the database.
Deleting Auditing Triggers
To delete a trigger:
Open the trigger that you want to delete.
Clear all the Audit options (Add, Change, and Delete).
Click Generate Code.
Drop the trigger name from the database.