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.