Skip to Main Content
Return to Navigation

Working With Auditing Triggers

This section discusses how to:

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 select PeopleTools, then select Utilities, then select Audit, then select Update Database Level Auditing.

Image: Audit Triggers page

This example illustrates the fields and controls on the Audit Triggers page.

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

  1. Select select PeopleTools, then select Utilities, then select Audit, then select Update Database Level Auditing.

  2. Click Add a New Value.

  3. Enter an existing base record.

  4. On the Audit Trigger page, you need to choose the record to hold the auditing data, the audit record.

  5. Select the events to audit, as in when data is added, changed, or deleted. You can select all of the options.

  6. Click Generate Code.

    This generates the SQL that ultimately creates the trigger.

  7. 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.

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 (select PeopleTools, then select Utilities, then select Audit, then select Perform Database Level Audit).

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:

  1. Select select PeopleTools, then select Utilities, then select Audit, then select Perform Database Level Audit.

  2. 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.

  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 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.

  4. 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:

  1. Select select PeopleTools, then select Utilities, then select Audit, then select 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.