Working With 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.
Access the Audit Triggers page .
Image: Audit Triggers page
This example illustrates the fields and controls on the Audit Triggers page.
|
Field or Control |
Definition |
|---|---|
| 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
Select
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.
Click Save.
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. You must use separate audit records for each record to be audited when using triggers to audit multiple records
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.
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 ().
|
Field or Control |
Definition |
|---|---|
| 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:
Select .
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.
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 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.
To delete a trigger:
Select .
Open the trigger that you want to delete.
Clear all the Audit options (Add, Change, and Delete).
Click Generate Code.
Click Save.
Drop the trigger name from the database.