Understanding Database Level Auditing
This chapter provides an overview of database level auditing and discusses how to:
Create audit record definitions.
Work with auditing triggers.
View audit information.
Create queries to view audit records details.
Use Microsoft SQL Server trigger information.
Use DB2 for z/OS trigger information.
Use DB2 for LUW trigger information.
Use Oracle trigger information.
Use Sybase trigger information.
Understanding Database Level Auditing
PeopleSoft provides trigger-based auditing functionality as an alternative to the record-based auditing that Application Designer provides. Some countries require that you audit changes to certain data, while some companies audit who is making changes to sensitive data. This level of auditing is not only for maintaining the integrity of the data, but it is also a heightened security measure. PeopleSoft takes advantage of database triggers (offered by most database vendors), and when a user makes a change to a specified field that you are monitoring, the changed data triggers the audit.
The information that a trigger records could include the user that made a change, the type of change that is made, when the change is made, and so on. Because the trigger records the user ID of the user who is modifying the base table, it is essential that you have the EnableDBMonitoring domain parameter set in PSADMIN to retrieve that information.
Note. If
you implement trigger-based auditing, be aware that there is an unavoidable
amount of additional overhead associated with auditing, which can effect the
system's overall performance.
EnableDBMonitoring isn't supported for Informix.
The elements that are involved with database level auditing are:
|
The base record is the record that you want to monitor, or audit, as in PS_ABSENCE_HIST. Presumably, the base record contains fields that you want to monitor. Limit the auditing of tables to the application tables and avoid auditing PeopleTools tables. |
|
|
The audit record is a custom record that you create with Application Designer. It stores the audit information for the fields on the base record that the trigger collects. Audit records begin with an AUDIT_ prefix. |
|
|
The trigger is the mechanism that a user invokes upon making a change to a specified field. The trigger stores the audit information in the audit table. PeopleSoft enables you to create triggers. A sample name for a trigger might be PS_ABSENCE_HIST_TR. |
Note. If you modify the record definition of the base record, then you must modify the audit record and re-create the associated trigger.
Creating Audit Record Definitions
To audit a record using triggers, you must create a record definition in Application Designer and build the SQL table in which you store audit information. When creating the audit record, remove any attributes, such as Parent records, Query Security Records, and PeopleCode.
The easiest way to create an audit table is to open the record definition of the base record that you want to audit. Save it as a new record, prefaced with AUDIT_.
Note. When you create a new audit record definition, be sure to name it with an AUDIT_ prefix. Some processes, such as the Employee ID Change and Employee ID Delete in PeopleSoft HRMS product line, make changes to certain fields, such as EMPLID. These processes do not affect any record definitions that begin with the AUDIT_ prefix, leaving the audit data secure.
Remove all edit and key attributes from the newly saved record. Add to the top of the audit record the following audit-specific fields:
AUDIT_OPRID
AUDIT_STAMP
AUDIT_ACTN
Make these fields required and keys. The following table explains the purpose of each audit-specific field.
Note. When you add these fields to the audit record, add them in the same order that they appear in the following table.
|
Audit Field Name |
Purpose |
|
AUDIT_OPRID |
Identifies the user who causes the system to trigger the audits, either by performing an add, change, or delete to an audited field. |
|
AUDIT_STAMP |
Identifies the date and time that the audit is triggered. |
|
AUDIT_ACTN |
Indicates the type of action the system audited. Possible action values include:
|
The audit table does not have to include all the columns of the base table. In fact, for performance reasons, it's best to only include those fields in the audit record that are deemed sensitive or significant. When adding fields to the audit record, PeopleSoft recommends that you conform to the order that they appear in the base record.
Note. This functionality allows for the Microsoft SQL Server requirement of not including ntext, text columns in the trigger syntax, as well as Oracle's requirement to exclude the LONG data type from audit records.
The following example compares the base table to the audit table, showing the audit-specific fields and the fields that are to be audited in the audit table.
|
AUDIT_OPRID |
|
|
AUDIT_STAMP |
|
|
AUDIT_ACTN |
|
|
EMPLID |
EMPLID |
|
ABSENCE_TYPE |
ABSENCE_TYPE |
|
BEGIN_DT |
|
|
RETURN_DT |
|
|
DURATION_DAYS |
|
|
DURATION_HOURS |
|
|
REASON |
REASON |
|
PAID_UNPAID |
|
|
EMPLOYER_APPROVED |
|
|
COMMENTS |
Once you save the record definition, you need to run the SQL Build procedure to build the SQL table in the relational database management system (RDBMS).
Following is an example of a SQL script for an audit record that audits the PS_ABSENCE_HIST table:
-- WARNING: -- -- This script should not be run in Data Mover. It may contain platform -- specific syntax that Data Mover is unable to comprehend. Please use the -- SQL query tool included with your database engine to process this script. -- USE PT8A go SET IMPLICIT_TRANSACTIONS ON go IF EXISTS (SELECT 'X' FROM SYSOBJECTS WHERE TYPE = 'U' AND NAME = 'PS_AUDIT_ABSENCE') DROP TABLE PS_AUDIT_ABSENCE go CREATE TABLE PS_AUDIT_ABSENCE (AUDIT_OPRID CHAR(8) NULL, AUDIT_STAMP PSDATETIME NOT NULL, AUDIT_ACTN CHAR(1) NOT NULL, EMPLID CHAR(11) NOT NULL, ABSENCE_TYPE CHAR(3) NOT NULL, BEGIN_DT PSDATE NULL, RETURN_DT PSDATE NULL, DURATION_DAYS SMALLINT NOT NULL, DURATION_HOURS DECIMAL(2,1) NOT NULL, REASON CHAR(30) NOT NULL, PAID_UNPAID CHAR(1) NOT NULL, EMPLOYER_APPROVED CHAR(1) NOT NULL) -- COMMENTS TEXT NULL) Text and Image Fields are not allowed go COMMIT Go
If COMMENTS is not allowed during the actual creation of the audit table, drop the column or do not choose the column when you create the audit table definition.
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 (PeopleTools, Utilities, Audit, Update Database Level Auditing).
|
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 PeopleTools, Utilities, Audit, Update Database Level Auditing.
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.
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 (PeopleTools, Utilities, Audit, 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:
Select PeopleTools, Utilities, Auditing, Perform Database Level Auditing.
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.
Deleting Auditing Triggers
To delete a trigger:
Select PeopleTools, Utilities, Audit, Update Database Level Auditing.
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.
Viewing Audit Information
Viewing the data that is in the audit record is important. That's why you're storing the information. Because the information resides in a table within the RDBMS, you can extract it and manipulate it to suit your reporting needs. This section provides samples of how the information appears in an audit record and some sample queries that you can construct with PeopleSoft Query.
The following example presents the contents of PS_AUDIT_ABSENCE after a trigger test:
AUDIT_OPRID AUDIT_STAMP AUDIT_ACTN EMPLID ⇒ ABSENCE_TYPE BEGIN_DT ----------- --------------------------- ---------- -----------⇒ ------------ ------------------------- BARNEY07 2000-01-11 16:25:13.380 A GORD ⇒ CNF 1981-09-12 00:00:00.000 BARNEY07 2000-01-11 16:25:36.123 K 8001 ⇒ CNF 1981-09-12 00:00:00.000 BARNEY07 2000-01-11 16:25:36.123 K 8001 ⇒ CNF 1983-03-02 00:00:00.000 BARNEY07 2000-01-11 16:25:36.123 K 8001 ⇒ CNF 1983-08-26 00:00:00.000 BARNEY07 2000-01-11 16:25:36.133 N 8001 ⇒ VAC 1981-09-12 00:00:00.000 BARNEY07 2000-01-11 16:25:36.133 N 8001 ⇒ VAC 1983-03-02 00:00:00.000 BARNEY07 2000-01-11 16:25:36.133 N 8001 ⇒ VAC 1983-08-26 00:00:00.000 BARNEY07 2000-01-11 16:25:40.790 D GORD ⇒ CNF 1981-09-12 00:00:00.000 RETURN_DT DURATION_DAYS DURATION_HOURS⇒ REASON PAID_UNPAID --------------------------- ------------- --------------⇒ ------------------------------ ----------- 1981-09-26 00:00:00.000 14 .0 ⇒ None P 1981-09-26 00:00:00.000 14 .0 ⇒ P 1983-03-07 00:00:00.000 6 .0 ⇒ P 1983-09-10 00:00:00.000 13 2.0 ⇒ P 1981-09-26 00:00:00.000 14 .0 ⇒ P 1983-03-07 00:00:00.000 6 .0 ⇒ P 1983-09-10 00:00:00.000 13 2.0 ⇒ P 1981-09-26 00:00:00.000 14 .0 ⇒ None P EMPLOYER_APPROVED COMMENTS ----------------- ----------------------------- Y This is the comments field Y Y Y Y This is an update Y This is an update Y This is an update Y
Note. For Microsoft SQL Server the AUDIT_OPRID field value will be NULL.
Creating Queries to View Audit Records Details
One way to view the information is to use PeopleSoft Query. This section assumes a working knowledge of PeopleSoft Query, and provides some sample queries that show the type of information that you can expect to view.
This section discusses how to:
Create an access group.
List all audit records in PS_AUDIT_ABSENCE.
List all audit records for a specified user ID.
List all audit records that contain an invalid OPRID.
List all audit records for a specified time period.
Creating an Access Group
To track audit records, it's useful to create an Access Group in Query Access Manager that contains all audit records. This makes it easier to access the audit records under PeopleSoft Query:
Listing All Audit Records in PS_AUDIT_JOB
Select all the fields from AUDIT_JOB. There are no extra criteria to add:
Listing All Audit Records for a Specified User ID
This query is similar to the previous one but with the following criteria added:
The example shows the prompt for properties the AUDIT_OPRID field:
Set up a prompt for User ID against the PSOPRDEFN table. That way, when you run the query, you can specify a particular user ID. In this case, the query focuses on User ID VP1:
Listing All Audit Records Containing an Invalid OPRID
This query is similar to the previous one, but you specify different criteria:
Click the Define/Edit Subquery link and select the OPRID field:
The subquery selects distinct User ID from PSOPRDEFN. This example shows the SQL for the query:
Listing All Audit Records for a Specified Time Period
This example shows a query containing the same fields as in the previous queries above, with different criteria:
Set the prompt properties to follow this example.
Change the AUDIT_STAMP field type to Date to enable the user to take advantage of the calendar control as a prompt mechanism.
Using Microsoft SQL Server Trigger Information
This section discusses how to:
Use Microsoft SQL Server trigger syntax.
Use Microsoft SQL Server to capture text/image columns.
Administer Microsoft SQL Server trigger maintenance.
Note. For Microsoft SQL Server, Image and Text Columns in tables can't be selected from the trigger tables INSERTED and DELETED.
Using Microsoft SQL Server Trigger SyntaxTo audit INSERTS, UPDATES, and DELETES of the records, use trigger with the following format:
Replace the names in emphasized text with the appropriate names for the trigger that you are constructing.
CREATE TRIGGER PS_ABSENCE_HIST_TR ON PS_ABSENCE_HIST FOR DELETE , INSERT , UPDATE AS SET NOCOUNT ON DECLARE @XTYPE CHAR(1), @OPRID CHAR(8) SET @OPRID = NULL [SELECT @OPRID = substring(cast(context_info as char(128)), 1,(charindex(',',cast(context_info as char(128)))-1)) FROM master..sysprocesses WHERE spid = @@spid] -- Determine Transaction Type IF EXISTS (SELECT * FROM DELETED) BEGIN SET @XTYPE = 'D' END IF EXISTS (SELECT * FROM INSERTED) BEGIN IF (@XTYPE = 'D') BEGIN SET @XTYPE = 'U' END ELSE BEGIN SET @XTYPE = 'I' END END -- Transaction is a Delete IF (@XTYPE = 'D') BEGIN INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID,getdate(),'D', EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM deleted END -- Transaction is a Insert IF (@XTYPE = 'I') BEGIN INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID,getdate(),'A', EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM inserted END -- Transaction is a Update IF (@XTYPE = 'U') BEGIN -- Before Update INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID,getdate(),'K', EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM deleted -- After Update INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID,getdate(),'N', EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM inserted END
Using Microsoft SQL Server to Capture Text/Image Columns
If you want to audit text or image columns with Microsoft SQL Server, you will have to alter the trigger scripts that are generated manually. The trigger scripts that generated through the online pages do not support text or image columns. Below is an example of how a join against the base table can capture the value of the COMMENTS field after an insert, or update is performed.
CREATE TRIGGER PS_ABSENCE_HIST_TR ON PS_ABSENCE_HIST FOR DELETE , INSERT , UPDATE AS SET NOCOUNT ON DECLARE @XTYPE CHAR(1), @OPRID CHAR(8) SET @OPRID = NULL [SELECT @OPRID = substring(cast(context_info as char(128)),1, (charindex(',',cast(context_info as char(128)))-1)) FROM master..sysprocesses WHERE spid = @@spid] IF EXISTS (SELECT * FROM DELETED) BEGIN SET @XTYPE = 'D' END IF EXISTS (SELECT * FROM INSERTED) BEGIN IF (@XTYPE = 'D') BEGIN SET @XTYPE = 'U' END ELSE BEGIN SET @XTYPE = 'I' END END -- Transaction is a Delete IF (@XTYPE = 'D') BEGIN INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED,COMMENTS) SELECT @OPRID,getdate(),'D', A.EMPLID,A.ABSENCE_TYPE,A.BEGIN_DT,A.RETURN_DT,A.DURATION_DAYS, A.DURATION_HOURS,A.REASON,A.PAID_UNPAID,A.EMPLOYER_APPROVED,'' FROM deleted A END -- Transaction is a Insert IF (@XTYPE = 'I') BEGIN INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED,COMMENTS) SELECT @OPRID,getdate(),'A', A.EMPLID,A.ABSENCE_TYPE,A.BEGIN_DT,A.RETURN_DT,A.DURATION_DAYS, A.DURATION_HOURS,A.REASON,A.PAID_UNPAID,A.EMPLOYER_APPROVED,B.COMMENTS FROM inserted A, PS_ABSENCE_HIST B WHERE A.EMPLID = B.EMPLID AND A.ABSENCE_TYPE = B.ABSENCE_TYPE AND A.BEGIN_DT = B.BEGIN_DT END -- Transaction is a Update IF (@XTYPE = 'U') BEGIN -- Before Update INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED,COMMENTS) SELECT @OPRID,getdate(),'K', A.EMPLID,A.ABSENCE_TYPE,A.BEGIN_DT,A.RETURN_DT,A.DURATION_DAYS, A.DURATION_HOURS,A.REASON,A.PAID_UNPAID,A.EMPLOYER_APPROVED,'' FROM deleted A -- After Update INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED,COMMENTS) SELECT @OPRID,getdate(),'N', A.EMPLID,A.ABSENCE_TYPE,A.BEGIN_DT,A.RETURN_DT,A.DURATION_DAYS, A.DURATION_HOURS,A.REASON,A.PAID_UNPAID,A.EMPLOYER_APPROVED,B.COMMENTS FROM inserted A, PS_ABSENCE_HIST B WHERE A.EMPLID = B.EMPLID AND A.ABSENCE_TYPE = B.ABSENCE_TYPE AND A.BEGIN_DT = B.BEGIN_DT END
Administering Microsoft SQL Server Trigger Maintenance
The following commands may be helpful when administering triggers.
List All Triggers in a Database
This command lists all triggers in a database:
SELECT name FROM sysobjects WHERE type = 'TR'
This command lists the trigger definition:
sp_helptext TRIGGERNAME
This command lists trigger information:
sp_helptrigger BASE TABLE NAME
Returns the type or types of triggers that are defined on the specified table for the current database.
sp_help TRIGGERNAME
Reports information about a database object (any object listed in the SYSOBJECTS table), a user-defined data type, or a data type that Microsoft SQL Server supplies.
To remove a trigger:
drop trigger TRIGGERNAME
To modify a trigger:
ALTER trigger ...
This alters the definition of a trigger that is created previously by the CREATE TRIGGER statement.
See the full definition in SQL Server Books Online.
See SQL Server Books Online for the full example.
To disable a trigger:
ALTER TABLE <table> | (ENABLE | DISABLE) TRIGGER (ALL | trigger_name[,...n])
(ENABLE | DISABLE ) TRIGGER - Specifies that trigger_name is enabled or disabled. When a trigger is disabled, it is still defined for the table; however, when INSERT, UPDATE or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.
ALL: Specifies that all triggers in the table are enabled or disabled.
trigger_nam: Specifies the name of the trigger to disable or enable.
Using DB2 UDB for z/OS Trigger Information
This section provides an overview of DB2 z/OS trigger information and discusses:
Verifying monitor trace setting.
DB2 z/OS trigger syntax.
DB2 z/OS trigger maintenance
Understanding DB2 z/OS Trigger InformationThe following topics describe the syntax, and commands, involved with DB2 z/OS triggers.
Before the Trigger Audit can be implemented on DB2 z/OS, the trigger statement must be defined.
DB2 z/OS Trigger Syntax
A trigger for each SQL operation type, as in INSERT, UPDATE and DELETE, needs to be defined separately with a different trigger name for a given triggering table. The allowable trigger name length is eight characters long. The following SQL is a sample of the trigger syntax.
--For INSERT operation: CREATE TRIGGER PSO112 AFTER INSERT ON PS_ABSENCE_HIST REFERENCING NEW AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER), CURRENT TIMESTAMP,'A', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); --For DELETE operation CREATE TRIGGER PSP112 AFTER DELETE ON PS_ABSENCE_HIST REFERENCING OLD AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER), CURRENT TIMESTAMP,'D', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); --For UPDATE operation CREATE TRIGGER PSQ112 AFTER UPDATE ON PS_ABSENCE_HIST REFERENCING NEW AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER), CURRENT TIMESTAMP,'N', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); CREATE TRIGGER PSR112 AFTER UPDATE ON PS_ABSENCE_HIST REFERENCING OLD AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER), CURRENT TIMESTAMP,'K', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED);
DB2 z/OS Trigger Maintenance
These commands might be useful for administering triggers.
List All Triggers in a Database
To list all triggers:
SELECT name FROM SYSIBM.SYSTRIGGERS
To list the trigger definition:
SELECT text FROM SYSIBM.SYSTRIGGERS WHERE NAME = trigger_name
To list the trigger information:
SELECT text FROM SYSIBM.SYSTRIGGERS WHERE NAME = trigger_name
To remove a trigger:
DROP trigger TRIGGERNAME restrict
You can't alter a trigger. You must drop it and recreate it.
See DB2 for z/OS SQL Reference.
See DB2 for z/OS Application Programming and SQL Guide.
Using DB2 UDB for Linux, Unix, and Windows (LUW) Trigger Information
This section discusses :
DB2 LUW trigger syntax.
DB2 LUW trigger maintenance.
DB2 LUW Trigger Syntax
A trigger for each SQL operation type, as in INSERT, UPDATE and DELETE, needs to be defined separately with a different trigger name for a given triggering table. The following SQL is a sample of the trigger syntax:
CREATE TRIGGER PSO1 AFTER INSERT ON PS_ABSENCE_HIST REFERENCING NEW AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE_HIST VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),CURRENT TIMESTAMP,'A', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); CREATE TRIGGER PSP1 AFTER DELETE ON PS_ABSENCE_HIST REFERENCING OLD AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE_HIST VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),CURRENT TIMESTAMP,'D', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); CREATE TRIGGER PSQ1 AFTER UPDATE ON PS_ABSENCE_HIST REFERENCING NEW AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE_HIST VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),CURRENT TIMESTAMP,'N', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED); CREATE TRIGGER PSR1 AFTER UPDATE ON PS_ABSENCE_HIST REFERENCING OLD AS C_ROW FOR EACH ROW MODE DB2SQL INSERT INTO PS_AUDIT_ABSENCE_HIST VALUES (COALESCE(NULLIF(CURRENT CLIENT_USERID,''),USER),CURRENT TIMESTAMP,'K', C_ROW.EMPLID, C_ROW.ABSENCE_TYPE, C_ROW.BEGIN_DT, C_ROW.RETURN_DT, C_ROW.DURATION_DAYS, C_ROW.DURATION_HOURS, C_ROW.REASON, C_ROW.PAID_UNPAID, C_ROW.EMPLOYER_APPROVED);
DB2 LUW Trigger Maintenance
These commands might be useful for administering triggers.
List All Triggers in a Database
To list all triggers:
SELECT trigname, trigevent, tabname FROM syscat.triggers
To list the trigger definition:
SELECT trigname, text FROM syscat.triggers
To remove a trigger:
DROP trigger TRIGGERNAME
See DB2 Universal Database for Linux, Unix, and Windows SQL Reference.
See DB2 Universal Database for Linux, Unix, and Windows Application Programming and SQL Guide.
Using Oracle Trigger Information
This section discusses how to:
Use Oracle trigger syntax.
Maintaining Oracle triggers.
The triggers that are generated on the Oracle platform reference a function that PeopleSoft delivers to obtain the PS_OPRID. This function must be installed into the Oracle database schema for the PeopleSoft database prior to creating the trigger. This function can be installed by executing the following SQL as the PeopleSoft database owner ID:
$PS_HOME\scripts\getpsoprid.sql
Using Oracle Trigger SyntaxThis example shows the Oracle trigger syntax.
drop function GET_PS_OPRID / create function GET_PS_OPRID (v_client_info VARCHAR2 ) return VARCHAR2 is i integer; /* Title: GET_PS_OPRID */ /* Purpose: Retrieves the operator id (OPRID) */ /* from a VARCHAR2 comma separated field */ /* of the format 'OPRID,OS_USER,MACHINE' */ /* If no OPRID is found, it returns '!NoOPRID' */ /* Limitations: (any grants, privileges, etc) */ /* Who: PeopleSoft Inc. */ /* Date: 2000-04-07 */ begin if ( length(v_client_info) IS NULL ) then return('!NoOPRID'); end if; if ( substr(v_client_info,1,1) = ',' ) then return('!NoOPRID'); end if; i := 1; while ( (substr(v_client_info,i,1)) <> ',' and i < 10) loop i := i + 1; end loop; if ( i > 9 ) then return('!NoOPRID'); else i := i - 1; return (substr (v_client_info, 1, i)); end if; end GET_PS_OPRID; / grant execute on GET_PS_OPRID to public / /* If Transaction is an Insert Or Update */ /* Capture After Values */ /* If Transaction is a Delete or Update */ /* Capture Before Values */ CREATE OR REPLACE TRIGGER PS_ABSENCE_HIST_TR AFTER INSERT OR UPDATE OR DELETE ON PS_ABSENCE_HIST FOR EACH ROW DECLARE V_AUDIT_OPRID VARCHAR2(64); BEGIN DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID); IF :OLD.EMPLID IS NULL THEN INSERT INTO PS_AUDIT_ABSENCE VALUES ( GET_PS_OPRID(V_AUDIT_OPRID) , SYSDATE , 'A' , :NEW.EMPLID , :NEW.ABSENCE_TYPE , :NEW.BEGIN_DT , :NEW.RETURN_DT , :NEW.DURATION_DAYS , :NEW.DURATION_HOURS , :NEW.REASON , :NEW.PAID_UNPAID , :NEW.EMPLOYER_APPROVED ); ELSE IF :NEW.EMPLID IS NULL THEN INSERT INTO PS_AUDIT_ABSENCE VALUES ( GET_PS_OPRID(V_AUDIT_OPRID) , SYSDATE , 'D' , :OLD.EMPLID , :OLD.ABSENCE_TYPE , :OLD.BEGIN_DT , :OLD.RETURN_DT , :OLD.DURATION_DAYS , :OLD.DURATION_HOURS , :OLD.REASON , :OLD.PAID_UNPAID , :OLD.EMPLOYER_APPROVED ); ELSE INSERT INTO PS_AUDIT_ABSENCE VALUES ( GET_PS_OPRID(V_AUDIT_OPRID) , SYSDATE , 'K' , :OLD.EMPLID , :OLD.ABSENCE_TYPE , :OLD.BEGIN_DT , :OLD.RETURN_DT , :OLD.DURATION_DAYS , :OLD.DURATION_HOURS , :OLD.REASON , :OLD.PAID_UNPAID , :OLD.EMPLOYER_APPROVED ); INSERT INTO PS_AUDIT_ABSENCE VALUES ( GET_PS_OPRID(V_AUDIT_OPRID) , SYSDATE , 'N' , :NEW.EMPLID , :NEW.ABSENCE_TYPE , :NEW.BEGIN_DT , :NEW.RETURN_DT , :NEW.DURATION_DAYS , :NEW.DURATION_HOURS , :NEW.REASON , :NEW.PAID_UNPAID , :NEW.EMPLOYER_APPROVED ); END IF; END IF; END PS_ABSENCE_HIST_TR; /
Maintaining Oracle Triggers
The following command may be helpful with triggers.
List All Triggers in a Database
To list triggers:
SELECT TRIGGERNAME FROM USER_TRIGGERS;
Executed from Schema_owner_id
SELECT TRIGGERNAME FROM ALL_TRIGGERS;
Executed from SYSTEM
The following data dictionary views reveal information about triggers:
USER_TRIGGERS
SQL> descr user_triggers; Name Null? Type ------------------------------- -------- ---- TRIGGER_NAME NOT NULL VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(26) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) REFERENCING_NAMES VARCHAR2(87) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) TRIGGER_BODY LONG
ALL_TRIGGERS
SQL> desc all_triggers; Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) TRIGGER_NAME NOT NULL VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(26) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) REFERENCING_NAMES VARCHAR2(87) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) TRIGGER_BODY LONG
USER_TRIGGERS
SQL> descr user_triggers; Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) TRIGGER_NAME NOT NULL VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(26) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) REFERENCING_NAMES VARCHAR2(87) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) TRIGGER_BODY LONG
The new column, BASE_OBJECT_TYPE, specifies whether the trigger is based on DATABASE, SCHEMA, table, or view. The old column, TABLE_NAME, is null if the base object is not table or view.
The column ACTION_TYPE specifies whether the trigger is a call type trigger or a PL/SQL trigger.
The column TRIGGER_TYPE includes two additional values: BEFORE EVENT and AFTER EVENT, which are applicable only to system events.
The column TRIGGERING_EVENT includes all system and DML events.
To list the trigger definition:
Select Trigger_Name, Trigger_Body from USER_TRIGGERS where Trigger_name=trigger_name;
To list trigger information:
Select Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner, Table_Name, Referencing_Names,When_Clause, Status, Description, Trigger_Body from USER_TRIGGERS where Trigger_name=trigger_name;
To remove a trigger:
drop trigger TRIGGERNAME
On Oracle, to explicitly alter a trigger definition, use the CREATE OR REPLACE option. See a full explanation in the Oracle SQL Reference (CREATE TRIGGER).
By default, triggers are enabled when they're first created. Disable a trigger by using the ALTER TRIGGER statement with the DISABLE option.
For example, to disable the trigger named REORDER of the INVENTORY table, enter the following statement:
ALTER TRIGGER Reorder DISABLE;
All triggers that are associated with a table can be disabled with one statement by using the ALTER TABLE statement with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers that are defined for the INVENTORY table, enter the following statement:
ALTER TABLE Inventory DISABLE ALL TRIGGERS;
Using Sybase Trigger Information
This section discusses how to:
Use Sybase trigger syntax.
Use Sybase trigger maintenance.
Using Sybase Trigger SyntaxThis example shows the syntax for creating triggers on Sybase:
CREATE TRIGGER PS_ABSENCE_HIST_TR ON PS_ABSENCE_HIST FOR INSERT, UPDATE, DELETE AS BEGIN DECLARE @XTYPE CHAR(1), @OPRID CHAR(8) IF EXISTS (SELECT 'X' FROM deleted) SELECT @XTYPE = 'D' IF EXISTS (SELECT 'X' FROM inserted) BEGIN IF (@XTYPE = 'D') SELECT @XTYPE = 'U' ELSE SELECT @XTYPE = 'I' END SELECT @OPRID = substring(clientname, 1, charindex(',', clientname) - 1) FROM master..sysprocesses WHERE spid = @@spid -- Transaction is a Delete and the Delete Part of an Update IF (@XTYPE = 'D') OR (@XTYPE = 'U') BEGIN IF (@XTYPE = 'U') SELECT @XTYPE = 'B' INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID, getdate(), @XTYPE, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM deleted END -- Transaction is a Insert and the Insert Part of an Update IF (@XTYPE = 'I') OR (@XTYPE = 'B') BEGIN IF (@XTYPE = 'B') SELECT @XTYPE = 'A' INSERT INTO PS_AUDIT_ABSENCE (AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED) SELECT @OPRID,getdate(), @XTYPE, EMPLID,ABSENCE_TYPE,BEGIN_DT,RETURN_DT,DURATION_DAYS, DURATION_HOURS,REASON,PAID_UNPAID,EMPLOYER_APPROVED FROM inserted END END
Using Sybase Trigger Maintenance
Commands that are useful with the trigger feature include:
List All Triggers in a Database
To list all triggers:
SELECT name FROM sysobjects WHERE type = 'TR'
To list trigger definition
sp_helptext TRIGGERNAME
To list trigger information:
sp_help TRIGGERNAME
This command reports information about a database object (any object that is listed in the sysobjects table), a user-defined data type, or a data type that Microsoft SQL Server supplies.
To remove a trigger:
drop trigger TRIGGERNAME
To disable a trigger:
ALTER TABLE table | (ENABLE | DISABLE) TRIGGER ( trigger_name)
(ENABLE | DISABLE ) TRIGGER specifies that trigger_name is enabled or disabled. When a trigger is disabled, it is still defined for the table; however, when INSERT, UPDATE or DELETE statements are executed against the table, the actions that are in the trigger are not performed until the trigger is enabled.
ALL. Specifies that all triggers in the table are enabled or disabled.
trigger_name. Specifies the name of the trigger to disable or enable.