are maintained by PeopleTools, but which themselves are not maintained by PeopleTools. This is often as a result of performance tuning activities where you choose to use features of the Oracle database that PeopleSoft do not use because they are not available on other database platforms.
- Function Based Indexes: In Oracle it is possible to implement an index on a function. A typical example would be an index on UPPER(NAME) on PS_NAMES to facilitate case
- Record based auditing can be done with a database DML trigger, instead of the default functionality of the Application Server. This was implemented by PeopleSoft to improve performance of the auditing, and is a rare example of PeopleSoft coding specific code for each platform because the trigger DDL is slightly different.
In Oracle it is possible to build DDL triggers. Just as DML triggers fire when the data is changed, DDL triggers fire when particular DDL commands are issued. I have created a trigger called PSFT_DDL_LOCK (available from the Go-Faster website) that fires when an object is altered or dropped. In certain cases the trigger will raise an error, this causes the original DDL command to fail, and thus prevents loss of the unmanaged objects. If the table related to the object being dropped or altered is not managed by PeopleSoft (if it can't be found in PSRECDEFN), the trigger does not raise any error. Otherwise,
- If a trigger is being dropped or altered, and the name of that trigger does not start with PSU, then an error is raised. Triggers that are named PSU% are created by Application Designer for use with Mobile Agents If an index is dropped or altered, the trigger checks that it is defined in PeopleTools. Indexes that correspond to Unique, Duplicate and Alternate Keys in Application Designer (where the index name is 'PS', followed by either a digit or an underscore, followed by the record name) are ignored.
- If a table is dropped or altered, the DDL trigger checks than there are no user indexes or triggers not defined in PeopleSoft, nor any primary key constraints, materialized views or materialized view logs on the table. It also checks that the table or index is not partitioned, clustered, global temporary or index organised.
When an error is generated by this trigger during development or migration activities, it usually indicates that there is another database object that you need to consider before issuing the command that errorred. It is not simply a matter of disabling the trigger and trying again.
The trigger does have a couple of side effects.
- There are a number of SQL statements that are run in the trigger, and this does impact the performance of DDL commands. If you are dropping all the tables in a schema, then it would be advisable to disable the trigger. The trigger definitely needs the following function based index to be created on PSRECDEFN, because it needs to look up the PeopleSoft record from the table name.
ON psrecdefn (DECODE(sqltablename,' ','PS_'recname,sqltablename))
TABLESPACE PSINDEX PCTFREE 0;
- If a user index is removed from the PeopleTools definition before it is dropped, the trigger will raise an error. However, in such cases it would be better to keep the definition of the index in Application Designer and set the platform radio button to 'None' so that PeopleTools does not build it. That way a comment can be preserved to explain why the index is no longer necessary.
The behaviour can be reenabled like this:
The previous version of this trigger (called T_LOCK) did not have this capability, and so I suggested disabling the trigger, but this affected all sessions.