Capturing DDL for Database Objects Not Managed by PeopleTools

I have written before about the challenges of managing database objects and attributes of database objects that are not managed by PeopleTools Application Designer. I proposed a DDL trigger to prevent such objects being dropped or altered. However, sometimes it is necessary to temporarily disable this DDL trigger, such as during patch or upgrade release it is necessary to disable this trigger to apply the changes.

Now, I have another DDL trigger and a packaged procedure that captures the DDL to recreate objects that are recursively dropped (such as DML triggers on tables). The DDL is stored in a database table. This trigger can remain permanently enabled, and the table it maintains can be used to see what objects are missing, as well as holding the SQL to rebuild them.

An common example of where this is can be valuable is where a system uses database triggers to capture audit data.  This method is often preferred because it generally performs better than having the application server generate additional DML to the audit table, and also captures updates made in other processes.  PeopleSoft even deliver processes to generate the DML triggers that write to the audit tables.  However, if you alter the table in Application Designer, perhaps only because you are applying a PeopleSoft fix, and apply the changes to the database by recreating the table, then the trigger will be lost.  It is then up to the customer to make sure the audit trigger is replaced.  There is absolutely nothing to warn you that the trigger is lost, and the application will still function without the trigger, but your updates will not be audited.

When a table is dropped, the trigger calls a procedure in the package that checks for:

• indexes that are not managed by PeopleTools (such as function-based indexes),
• triggers not managed by PeopleTools (other than the PSU triggers created for mobile agents),
• materialised view logs.
• If the table is partitioned or global temporary the DDL for the object being dropped is also captured.

When an index is dropped the index check is performed. Similarly the DDL to rebuild partitioned indexes or indexes on Global Temporary tables is also captured.

When an object for which the DDL has been captured is explicitly dropped, this is indicated on the table GFC_REL_OBJ by storing the time at which it was dropped. When it is recreated this time-stamp is cleared.  Thus it is possible to decide whether something was deliberately or accidentally dropped.

Automatically Granting Privileges on Newly Created Tables

I saw an interesting question on the Oracle-L forum: We have PeopleSoft applications that create tables on the fly. Developers want access to those tables that will be created on the fly, in case the process that creates it ends abnormally. I looked into granting via DDL triggers, it seemed like granting access via them is a problem. Is there a way to grant access other than doing 'grant select any'.

I am finding it increasingly common for developers and support staff not to have direct access to the PeopleSoft OwnerID schema (SYSADM) in even development environments, but using personal database logins. They need to have SELECT privilege on tables. The problem described above also occurs when table is rebuilt by Application Designer. When it is dropped the granted privileges disappear with the table.

It is certainly true that you cannot issue DDL in DDL trigger on the same object that caused the trigger to fire. You will get an error caused by a deadlock in the recursive SQL.

CREATE OR REPLACE TRIGGER gfc_grant AFTER CREATE ON sysadm.schema
DECLARE
l_cmd VARCHAR2(1000 CHAR);
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
l_cmd := 'GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster';
dbms_output.put_line('DDL:'||l_cmd);
EXECUTE IMMEDIATE l_cmd;
END IF;
END;
/
show errors
set serveroutput on
DDL:GRANT SELECT ON SYSADM.T TO gofaster
CREATE TABLE t (a NUMBER)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 9

Some sites have regular maintenance jobs that recreate any missing privileges.

However, there is a way to have the privileges automatically recreated soon after the table is built. You could use a DDL trigger to submit a job to the Oracle job scheduler to grant the privileges. You can't submit DDL directly via the job scheduler, so you need a procedure to which you can pass the DDL as a string parameter, and then execute it as dynamic SQL in the procedure.

CREATE OR REPLACE PROCEDURE myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
/

Then the DDL trigger can submit a job to call this procedure with the GRANT command in the parameter.

CREATE OR REPLACE TRIGGER gfc_grant
AFTER CREATE ON sysadm.schema
DECLARE
l_jobno NUMBER;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster'');');
END IF;
END;
/

If I create this table

CREATE TABLE t (a NUMBER);

I get this job

SELECT * FROM dba_jobs
/

JOB LOG_USER
---------- ------------------------------------------------------------------------------------------
PRIV_USER
------------------------------------------------------------------------------------------
SCHEMA_USER                                                                                LAST_DATE
------------------------------------------------------------------------------------------ -------------------
LAST_SEC                 THIS_DATE           THIS_SEC                 NEXT_DATE
------------------------ ------------------- ------------------------ -------------------
NEXT_SEC                 TOTAL_TIME BRO
------------------------ ---------- ---
INTERVAL
---------------------------------------------------------------------------------------------------------------
FAILURES
----------
WHAT
---------------------------------------------------------------------------------------------------------------
60 SYSADM
SYSADM
SYSADM
19:04:52 05/03/2009
19:04:52                          0 N
null

myddl('GRANT SELECT ON SYSADM.T TO gofaster');

After the job has run, which should normally only be a few seconds, I get these privileges

SELECT * FROM user_tab_privs WHERE table_name = 'T';

GRANTEE
------------------------------------------------------------------------------------------
OWNER
------------------------------------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------------------------------------
GRANTOR
------------------------------------------------------------------------------------------
PRIVILEGE
---------------------------------------------------------------------------------------------------------------
GOFASTER
SYSADM
T
SYSADM
SELECT

Added 2.4.2009: Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model. See part 2.

Truncating a Table does not affect the Materialized View Log

I am working on a site that is replicating tables between databases using Materialized Views. I have realised that if a replicated table is truncated on the source database, that the rows remain in the materialized view on the target until a complete refresh is performed. Worse, if ROWID based Materialized View is used, then the fast refresh process will error with 'ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh'. In PeopleSoft, there are no primary keys, and it is not possible to build them if any of the key columns are nullable, which is the case with non-required date fields in PeopleSoft. Here is an example. I will create two tables, and replicate them with Materialized Views. One by primary key, and the other by ROWID.

CREATE TABLE t_pk
(a NUMBER
,b VARCHAR2(20)
,CONSTRAINT t PRIMARY KEY(a));

CREATE TABLE t_r
(a NUMBER
,b VARCHAR2(20));

CREATE MATERIALIZED VIEW LOG ON t_pk WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW t_pk_mv REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM t_pk ;

CREATE MATERIALIZED VIEW LOG ON t_r WITH ROWID;
CREATE MATERIALIZED VIEW t_r_mv REFRESH FAST WITH ROWID
AS SELECT * FROM t_r ;

INSERT INTO t_pk VALUES(1,'Old');
INSERT INTO t_pk VALUES(2,'Old');
INSERT INTO t_r VALUES(1,'Old');
INSERT INTO t_r VALUES(2,'Old');

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 Old

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

So the data has replicated and everything seems to working fine. Now lets truncate the source tables, and put some new data in. One row has the same key value, one does not.

TRUNCATE TABLE t_pk;
TRUNCATE TABLE t_r;

INSERT INTO t_pk VALUES(2,'New');
INSERT INTO t_pk VALUES(3,'New');
INSERT INTO t_r VALUES(2,'New');
INSERT INTO t_r VALUES(3,'New');

The fast refresh of the Materialized View with the primary key appears to work. The new rows are inserted into the Materialized View, replacing existing rows with the same key values, but the old rows remain.

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
PL/SQL procedure successfully completed.

SELECT * FROM t_pk;
A B
- ---
2 New
3 New

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 New
3 New

The fast refresh of the Materialized View by ROWID fails, and the old data remains in place.

BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

But rows 1 & 2 are in MV but not source table

SELECT * FROM t_r;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

However, a full refresh corrects the discrepancy

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'c'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'c'); END;

SELECT * FROM t_pk_mv;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
2 New
3 New

It seems to me, that it would be appropriate to prevent a Truncate command executing if there is a Materialized View Log on the table. I have written such a trigger

CREATE OR REPLACE TRIGGER mvtrunc_lock
BEFORE TRUNCATE
ON SYSADM.SCHEMA
DECLARE
 e_generate_message EXCEPTION;
 l_recname  VARCHAR2(15 CHAR);
 l_msg      VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2     VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent
   ||' '||lower(ora_dict_obj_type)
   ||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||'.  ';

 sql_text ora_name_list_t;
 l_sql_stmt VARCHAR2(1000 CHAR) := '';
 n          INTEGER;
 i          INTEGER;

BEGIN
 /*extract the originating SQL statement into a string variable*/
 n := ora_sql_txt(sql_text);
 FOR i IN 1..n LOOP
  l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
 END LOOP;

 IF ora_dict_obj_type = 'TABLE' AND 
    ora_sysevent = 'TRUNCATE' THEN

  BEGIN /*if a materialized view log exists*/
   SELECT 'There is a materialized view log.'
   INTO   l_msg
   FROM   all_mview_logs l
   WHERE  ROWNUM = 1
   AND    l.master = ora_dict_obj_name
   AND    l.log_owner = ora_dict_obj_owner
   ;
   RAISE e_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END;
 END IF;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN e_generate_message THEN
   RAISE_APPLICATION_ERROR(-20042,
     'MVTRUNC_LOCK:'||l_msg2||l_msg||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors

The trigger MVTRUNC_LOCK can be downloaded from the Go-Faster website.

DDL Triggers to prevent loss of database objects not managed by PeopleTools

Sometimes you have to certain database techniques or create database objects on tables that
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
  insensitive searching.
• 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.

However, these objects are not created by the DDL scripts built by the Application Designer, and can accidentally be lost when the table is altered with an 'alter by recname' script generated by Application Designer. The implications can be quite serious. If the auditing trigger were to be lost, then the application would continue to run, but no audit data would be produced, and no error would be raised.
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 psscripts github repository) 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 that 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.

If any of the tests fail, then the trigger raises an exception the DDL statement fails with an error message generated in the trigger. The SQL that generated the error is also included in the error text.
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 several 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.

CREATE INDEX pszpsrecdefn
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.

Updated 2.6.2011: The checking behaviour trigger can be disabled for just the current session by calling this packaged procedure that sets a package global variable which is read by a function called by the trigger. For the one session where this is done, the checks are not performed by trigger are disabled:
execute psft_ddl_lock.set_ddl_permitted(TRUE);

The behaviour can be reenabled like this:
execute psft_ddl_lock.set_ddl_permitted(FALSE);

The previous version of this trigger (called T_LOCK) did not have this capability, so I suggested disabling the trigger, but this affected all sessions.

Using DDL Triggers to protect database objects not managed by Application Designer

Sometimes it is necessary create certain database objects manually, and not manage them via the Application Designer. However, this can introduce some management problems. When an object is altered in the Application Designer, it is then necessary to build an alter script that may rebuild the whole table. It is easy for the additional objects to be accidentally lost. There are two main scenarios.

• Additional indexes and Function based indexes: During the course of performance tuning it is often necessary to build additional indexes. Ideally these indexes should be added via the Application Designer. However, a DBA might add an index directly to the production environment, and it may take time to get a project moved into that environment. The other scenario, is that the Application Designer cannot build function-based indexes. These are particularly useful to build upper case indexes on columns, to support case-insensitive searching.
• PeopleSoft delivers a mechanism to build triggers to perform DML auditing. However, if you then rebuild the underlying table, then the trigger will be lost, and there is nothing PeopleSoft to warn you of this, or to audit this situation.

My solution to both scenarios is to create a DDL trigger to prevent accidental ALTER and DROP commands on objects not defined by PeopleTools. When you do want to alter this objects you can then disable this trigger. So you now have a way of controlling when you can and can't drop additional triggers and indexes. You still have to remmeber to switch this trigger back on again!

CREATE OR REPLACE TRIGGER t_lock 
BEFORE DROP OR ALTER
ON SYSADM.SCHEMA
DECLARE
 l_generate_message EXCEPTION;
 l_recname VARCHAR2(15 CHAR);
 l_msg VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2 VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent||' '||lower(ora_dict_obj_type)||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
BEGIN
 IF ora_dict_obj_type = 'TABLE' THEN
  SELECT r.recname
  INTO   l_recname
  FROM   psrecdefn r
  WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
  ;

  BEGIN
   SELECT 'Trigger '||t.trigger_name||' exists on table '||ora_dict_obj_name||'.'
   INTO   l_msg
   FROM   all_triggers t
   WHERE  ROWNUM = 1
   AND    t.table_name    = ora_dict_obj_name
   AND    t.table_owner   = ora_dict_obj_owner
   AND    t.trigger_name != 'PSU'||l_recname
   ;
   RAISE l_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

  BEGIN
   SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
   INTO   l_msg
   FROM   all_indexes i
   WHERE  ROWNUM = 1
   AND    i.table_name  = ora_dict_obj_name
   AND    i.table_owner = ora_dict_obj_owner
   AND NOT EXISTS(
    SELECT 'x'
    FROM   psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
    WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
    AND    r.recname = p.recname
    AND    p.fieldname = f.fieldname 
    AND    (f.recname = p.recname_parent
     OR     (MOD(f.useedit/1,2)=1 /*key*/ 
     OR     MOD(f.useedit/2,2)=1 /*dup*/ 
     OR     MOD(f.useedit/16,2)=1 /*alt*/))
    AND    j.recname = p.recname_parent
    AND    'PS'||j.indexid||r.recname = i.index_name
   )
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 ELSIF ora_dict_obj_type = 'TRIGGER' THEN

  BEGIN
   SELECT 'Trigger '||t.trigger_name||' exists on PeopleSoft record '||r.recname||'.'
   INTO   l_msg
   FROM   all_triggers t, psrecdefn r
   WHERE  ROWNUM = 1
   AND    t.trigger_name = ora_dict_obj_name
   AND    t.owner        = ora_dict_obj_owner
   AND    t.table_owner  = ora_dict_obj_owner
   AND    DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = t.table_name
   AND    t.trigger_name != 'PSU'||r.recname
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 ELSIF ora_dict_obj_type = 'INDEX' THEN

  BEGIN
   SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
   INTO   l_msg
   FROM   all_indexes i
   WHERE  ROWNUM = 1
   AND    i.index_name = ora_dict_obj_name
   AND    i.owner      = ora_dict_obj_owner
   AND NOT EXISTS(
    SELECT 'x'
    FROM   psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
    WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
    AND    r.recname = p.recname
    AND    p.fieldname = f.fieldname 
    AND    (f.recname = p.recname_parent
     OR     (MOD(f.useedit/1,2)=1 /*key*/ 
     OR     MOD(f.useedit/2,2)=1 /*dup*/ 
     OR     MOD(f.useedit/16,2)=1 /*alt*/))
    AND    j.recname = p.recname_parent
    AND    'PS'||j.indexid||r.recname = i.index_name
   )
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 END IF;

EXCEPTION
 WHEN NO_DATA_FOUND THEN 
  NULL;
 WHEN l_generate_message THEN
  Raise_application_error(-20042,'T_LOCK: '||l_msg||'  '||l_msg2);
END;
/

Notes:

• The trigger performs a number of test queries on PeopleTools or Oracle Catalogue views. There are different queries depending upon the type of object being ALTERed or DROPped. If a query returns a row then there is a problem, and an exception is raised. The queries also generate part of the error message that is returned.
• The trigger only affects operations on tables that are specified in PSRECDEFN as type 0 or 7 records. I have not added an handling for the additional instances of a PeopleSoft temporary table.
• The ora_% variables are described in the RDBMS documentation in Application Developer's Guide - Fundamentals. In the 9.2 documentation this is in chapter 16. Working with System Events Event Attribute Functions

So here are a few tests. I have created a function based index and a two triggers on PS_RT_RATE_TBL. I have chosen this table for the example because it also has a PeopleSoft generated trigger for Mobile agents.

CREATE INDEX DMK_RT_RATE_TBL 
ON PS_RT_RATE_TBL(UPPER(RT_RATE_INDEX));
Index created.
CREATE OR REPLACE TRIGGER pstrt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
 raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER psurt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
 raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.

So the t_lock trigger prevents me from droping or altering either the function based index or the table.

DROP TABLE PS_RT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot DROP table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

ALTER TABLE PS_RT_RATE_TBL RENAME to DMK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot ALTER table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

DROP INDEX DMKZRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Index DMKZRT_RATE_TBL is managed outside PeopleTools. Cannot DROP index
SYSADM.DMKZRT_RATE_TBL
ORA-06512: at line 99 

But I can drop any other index on the table that is maintained by PeopleTools.

DROP INDEX PS_RT_RATE_TBL
Index dropped. 

I can't alter any trigger on the table except the PSU trigger that is created by Application Designer for objects that are maintained by mobile agents.

ALTER TRIGGER PSTRT_RATE_TBL DISABLE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot ALTER trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

DROP TRIGGER PSTRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot DROP trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

ALTER TRIGGER PSURT_RATE_TBL DISABLE;
Trigger altered.

DROP TRIGGER PSURT_RATE_TBL;
Trigger dropped.